PivotTableWizard SourceData question
 

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.

PivotTableWizard SourceData question
 

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(Tr ue,True,xlR1C1,True)
Next i

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Microsoft Forum wrote:
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.

PivotTableWizard SourceData question
 

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" wrote in message
...
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(Tr ue,True,xlR1C1,True)
Next i

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Microsoft Forum wrote:
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.

PivotTableWizard SourceData question
 

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.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com


Microsoft Forum wrote:
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" wrote in message
...
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(Tr ue,True,xlR1C1,True)
Next i

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Microsoft Forum wrote:
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.