VBA for Pivot Table Subtotal Location

[mauricio1013]

I have tried about a million different ways to write the code to have my macro automatically set the pivot table subtotals to the bottom. It seems like it would be easy, but it isn't!

Here is what i get when i record the macro:

Sub SubTotalLocation_Bottom()
'
' SubTotalLocation_Bottom Macro
'
Sheets("Strike Report").Select
Range("A3").Select
ActiveSheet.PivotTables("MyPT").SubtotalLocation = xlAtBottom

End Sub

When I run that sub i get the following error message:

Run-time error '438':
Object doesn't support this property or method

Anyone have a clue? This is driving me nuts?

 

[Jerry Sullivan]

Hi and Welcome to the Board,

Your syntax is just slightly off.
Change: .SubtotalLocation = xlAtBottom

To: .SubtotalLocation(xlAtBottom)

You can also streamline it a bit by removing the .Select and
directly referencing the objects....

Sheets("Strike Report").PivotTables("MyPT").SubtotalLocation (xlAtBottom)

 

[mauricio1013]

Thank you for your response. It worked like a charm.