Passing string as array variable (Pivot VBA)
 

Hi, I am trying to create a pivot table with vba by passing rowfields,
columnfields, and pagefields as variables. The code below works if I
have a single value as for the columnfield or for the rowfield. But
for pagefields, I need to pass two values (as in Line#13). I would
like to use Line#12 but am not sure how to get pass the two values to
the pfd variable.
Thanks!

Dim rfd As Variant
Dim cfd As Variant
Dim pfd As Variant

Line#1 Sub Macro4()
Line#2 rfd = "Date"
Line#3 cfd = "SKU"
Line#4 pfd = "" & "Region" & """" & "," & """" & "Franchise Store" & ""
Line#5
Line#6 ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
Line#7 "'SourceData (2)'!R1C1:R5000C5").CreatePivotTable
TableDestination:="", _
Line#8 TableName:="PivotTable3",
DefaultVersion:=xlPivotTableVersion10
Line#9 ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
Line#10 ActiveSheet.Cells(3, 1).Select
Line#11 ActiveSheet.PivotTables("PivotTable3").AddFields
RowFields:=rfd, _
Line#12 ColumnFields:=cfd, PageFields:=Array(pfd)
Line#13 'ColumnFields:=cfd, PageFields:=Array("Franchise
Store", "Region")
Line#14
ActiveSheet.PivotTables("PivotTable3").PivotFields ("Inventory").Orientation
= _
Line#15 xlDataField
Line#16 End Sub

Passing string as array variable (Pivot VBA)
 

Since excel is looking for an array, you have to pass it an array. The
array(pfd) you're passing is a single element of that long string--not two
elements (of shorter strings).

Why not:

Dim pfd as Variant
pfd = array("Region", "Franchise Store")
...., PageFields:=Array(pfd)



klingongardener wrote:

Hi, I am trying to create a pivot table with vba by passing rowfields,
columnfields, and pagefields as variables. The code below works if I
have a single value as for the columnfield or for the rowfield. But
for pagefields, I need to pass two values (as in Line#13). I would
like to use Line#12 but am not sure how to get pass the two values to
the pfd variable.
Thanks!

Dim rfd As Variant
Dim cfd As Variant
Dim pfd As Variant

Line#1 Sub Macro4()
Line#2 rfd = "Date"
Line#3 cfd = "SKU"
Line#4 pfd = "" & "Region" & """" & "," & """" & "Franchise Store" & ""
Line#5
Line#6 ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
Line#7 "'SourceData (2)'!R1C1:R5000C5").CreatePivotTable
TableDestination:="", _
Line#8 TableName:="PivotTable3",
DefaultVersion:=xlPivotTableVersion10
Line#9 ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
Line#10 ActiveSheet.Cells(3, 1).Select
Line#11 ActiveSheet.PivotTables("PivotTable3").AddFields
RowFields:=rfd, _
Line#12 ColumnFields:=cfd, PageFields:=Array(pfd)
Line#13 'ColumnFields:=cfd, PageFields:=Array("Franchise
Store", "Region")
Line#14
ActiveSheet.PivotTables("PivotTable3").PivotFields ("Inventory").Orientation
= _
Line#15 xlDataField
Line#16 End Sub

--

Dave Peterson

Passing string as array variable (Pivot VBA)
 

Dave:
Thank you so much. Your solution works beautifully.
Merry X'Mas!

Dave Peterson wrote:
Since excel is looking for an array, you have to pass it an array. The
array(pfd) you're passing is a single element of that long string--not two
elements (of shorter strings).

Why not:

Dim pfd as Variant
pfd = array("Region", "Franchise Store")
..., PageFields:=Array(pfd)



klingongardener wrote:

Hi, I am trying to create a pivot table with vba by passing rowfields,
columnfields, and pagefields as variables. The code below works if I
have a single value as for the columnfield or for the rowfield. But
for pagefields, I need to pass two values (as in Line#13). I would
like to use Line#12 but am not sure how to get pass the two values to
the pfd variable.
Thanks!

Dim rfd As Variant
Dim cfd As Variant
Dim pfd As Variant

Line#1 Sub Macro4()
Line#2 rfd = "Date"
Line#3 cfd = "SKU"
Line#4 pfd = "" & "Region" & """" & "," & """" & "Franchise Store" & ""
Line#5
Line#6 ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
Line#7 "'SourceData (2)'!R1C1:R5000C5").CreatePivotTable
TableDestination:="", _
Line#8 TableName:="PivotTable3",
DefaultVersion:=xlPivotTableVersion10
Line#9 ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
Line#10 ActiveSheet.Cells(3, 1).Select
Line#11 ActiveSheet.PivotTables("PivotTable3").AddFields
RowFields:=rfd, _
Line#12 ColumnFields:=cfd, PageFields:=Array(pfd)
Line#13 'ColumnFields:=cfd, PageFields:=Array("Franchise
Store", "Region")
Line#14
ActiveSheet.PivotTables("PivotTable3").PivotFields ("Inventory").Orientation
= _
Line#15 xlDataField
Line#16 End Sub

--

Dave Peterson