Macro code for settings

Almost every pivot table I do, I have to manually alter the following settings using the menu bar.

Subtotals - Set to 'Do not show subtotals
Grand Totals - Set to 'Off for Rows and Columns'
Report Layout - Set to 'Show in Tabular Form'

I tried recording these steps into a Macro with no success. Are there any Macro steps I can use ?

If not, is there a way that I can set all my Pivots to start with the above setup ?

Hope someone can help.

Thanks

Tdub

Hi,

Searching the net, it looks like Power Pivots might give you a chance.

https://support.office.com/en-us/art...0-91F5140A4F25

Other than writing VBA, I don't see a way.
I've recorded a macro to do your above steps and it keys on the single pivot table and then each field to do the settings. In VBA a "For Each" loop might generalize these steps but we should have a sample file to do it with.

Thank you for looking at this Marvin. You're right I think VBA is the way to go. I've found two pieces of code which do No Subtotals or Grand Totals. So I'm 2/3rds of the way there..

I've got the tabular row code I think. I'm just scratching my head now to include this in a macro that does all 3 tasks on the current pivot table.

ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlTabularRow

Here is the code for the Hide subtotals and grand totals. They both work
Sub PivotTableLayout()
Dim PvtTbl As PivotTable
Dim pvtFld As PivotField

Set PvtTbl = ActiveSheet.PivotTables(1)

'hide Subtotals for all fields in the PivotTable .

With PvtTbl
For Each pvtFld In .PivotFields
pvtFld.Subtotals(1) = True
pvtFld.Subtotals(1) = False
Next pvtFld
End With
End Sub


Sub PivotGrandTotals()
'PURPOSE: Show setup for various Pivot Table Grand Total options
'SOURCE: www.TheSpreadsheetGuru.com

Dim pvt As PivotTable

Set pvt = ActiveSheet.PivotTables(1)

'Off for Rows and Columns
pvt.ColumnGrand = False
pvt.RowGrand = False

'On for Rows and Columns
'pvt.ColumnGrand = True
'pvt.RowGrand = True

'On for Rows only
'pvt.ColumnGrand = False
'pvt.RowGrand = True

'On for Columns Only
'pvt.ColumnGrand = True
'pvt.RowGrand = False

End Sub