PivotTableWizard SourceData question

  • Thread starter Thread starter Microsoft Forum
  • Start date Start date
M

Microsoft Forum

Hi all,

In the VBA online help it says that the SourceData property accepts "an
array of ranges". If this is true, may anyone advise why the following code
failed? Thanks.

Sub CreatePivot()
Dim MonthlyData(1 To 12) As Range
Dim i As Integer

For i = 1 To 12
Set MonthlyData(i) = Worksheets(i).Range("A1").CurrentRegion
Next

ActiveSheet.PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:=MonthlyData, _
tablename:="YearlySales"

End Sub

Frederick Chow
Hong Kong.
 
Frederick

The help lies. It says "array of ranges" but it should say "array of
strings that are valid external cell addresses". You need to change your
SourceType to xlConsolidation and change your loop to

Dim MonthlyData(1 to 12) as String

For i = 1 to 12
MonthlyData(i) =
Worksheets(i).Range("A1").CurrentRegion.Address(True,True,xlR1C1,True)
Next i
 
Hi Dick,

Thanks for your response, and I modifed my macro as advised but the macro
still failed at the PivotTableWizard method. May you give me futher adivce?

Sub CreatePivot()
Dim MonthlyData(1 To 12) As String
Dim i As Integer

For i = 1 To 12
MonthlyData(i) = Worksheets(i).Range("A1").CurrentRegion.Address _
(True, True, xlR1C1, True)
Next

ActiveSheet.PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:=MonthlyData, _
tablename:="YearlySales"

End Sub

Frederick Chow
 
Frederick

Change this
SourceType:=xlDatabase, _
Click to expand...

to this

SourceType:=xlConsolidation, _

If that doesn't fix it, be sure to include the error message when you post
back.
 
Back
Top