PivotTableWizard SourceData question

[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.

 

[Dick Kusleika]

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

 

[Microsoft Forum]

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

 

[Dick Kusleika]

Frederick

Change this

SourceType:=xlDatabase, _

to this

SourceType:=xlConsolidation, _

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