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.

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

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-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.

Hi ****,

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

"**** Kusleika" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-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.
>
>

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.

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com


Microsoft Forum wrote:
> Hi ****,
>
> 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
>
> "**** Kusleika" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>> --
>> **** Kusleika
>> Excel MVP
>> Daily Dose of Excel
>> www.*****-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.