Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PivotTableWizard help with VB | Excel Worksheet Functions | |||
Dynamic sourcedata when copying chart within sheet | Charts and Charting in Excel | |||
SourceData in pivot table | Excel Programming | |||
Change SourceData in Excel PivotTable via VBA | Excel Programming | |||
Setting up a SourceData:= in VB | Excel Programming |