Pivot Table - Macro to add all fields? Help!

hammercat

New Member
Joined
Apr 30, 2014
Messages
1
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!

 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Forum statistics

Threads
1,215,054
Messages
6,122,893
Members
449,097
Latest member
dbomb1414

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top