Hello!
I'm struggling with a Macro / Pivot problem in Excel - I'm hoping you can help!
The problem
I have recorded a macro that works as intended, with the exception of it automatically adding 'all column fields' to the Pivot table.
I'm afraid that the columns are likely to be variable each time I run the macro. As it stands, there are c.100 columns, so ticking them all individually is very time consuming.
The Code
Here is the output from the Macro Recorder (code for review in BOLD):
Sheets("LegacyLGReport").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table2", Version:=xlPivotTableVersion10).CreatePivotTable TableDestination _
:="PIVOTOLD!R3C2", TableName:="PivotTable10", DefaultVersion:= _
xlPivotTableVersion10
Sheets("PIVOTOLD").Select
Cells(3, 2).Select
With ActiveSheet.PivotTables("PivotTable10").PivotFields("Mobile Number")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable10").AddDataField ActiveSheet.PivotTables( _
"PivotTable10").PivotFields("4GB Data and WiFi"), "Count of 4GB Data and WiFi" _
, xlCount
ActiveSheet.PivotTables("PivotTable10").AddDataField ActiveSheet.PivotTables( _
"PivotTable10").PivotFields("4GEE Mobile Broadband 16GB"), _
"Count of 4GEE Mobile Broadband 16GB", xlCount
With ActiveSheet.PivotTables("PivotTable10").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable10").DataPivotField
.Orientation = xlRowField
.Position = 1
End With
End Sub
Here is what I would like to achieve (sorry for the simplistic approach - I don't know how to code)
ActiveSheet.PivotTables("PivotTable10").AddDataField ActiveSheet.PivotTables( _
"PivotTable10").PivotFields.ADD ALL COLUMNS, "DISPLAY ASSOCIATED COLUMN HEADINGS" _
, xlCount
THANK YOU to anyone who can help with this riddle!
I'm struggling with a Macro / Pivot problem in Excel - I'm hoping you can help!
The problem
I have recorded a macro that works as intended, with the exception of it automatically adding 'all column fields' to the Pivot table.
I'm afraid that the columns are likely to be variable each time I run the macro. As it stands, there are c.100 columns, so ticking them all individually is very time consuming.
The Code
Here is the output from the Macro Recorder (code for review in BOLD):
Sheets("LegacyLGReport").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table2", Version:=xlPivotTableVersion10).CreatePivotTable TableDestination _
:="PIVOTOLD!R3C2", TableName:="PivotTable10", DefaultVersion:= _
xlPivotTableVersion10
Sheets("PIVOTOLD").Select
Cells(3, 2).Select
With ActiveSheet.PivotTables("PivotTable10").PivotFields("Mobile Number")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable10").AddDataField ActiveSheet.PivotTables( _
"PivotTable10").PivotFields("4GB Data and WiFi"), "Count of 4GB Data and WiFi" _
, xlCount
ActiveSheet.PivotTables("PivotTable10").AddDataField ActiveSheet.PivotTables( _
"PivotTable10").PivotFields("4GEE Mobile Broadband 16GB"), _
"Count of 4GEE Mobile Broadband 16GB", xlCount
With ActiveSheet.PivotTables("PivotTable10").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable10").DataPivotField
.Orientation = xlRowField
.Position = 1
End With
End Sub
Here is what I would like to achieve (sorry for the simplistic approach - I don't know how to code)
ActiveSheet.PivotTables("PivotTable10").AddDataField ActiveSheet.PivotTables( _
"PivotTable10").PivotFields.ADD ALL COLUMNS, "DISPLAY ASSOCIATED COLUMN HEADINGS" _
, xlCount
THANK YOU to anyone who can help with this riddle!