leaderla.blogg.se - How to remove subtotals from pivot table macro

Since I’m only interested in turning subtotals off, I can focus on the Automatic setting, and ignore all the others. Here is the pivot table with the custom subtotals. However, if I change the first item to False, the Custom subtotals for Sum, Count and Average are shown, when I run the code, because Automatic subtotals are turned off. If I run that code, only the Automatic subtotals will appear. Here is another version of the code, for the Category field, with the first 3 items changed to True: ActiveSheet.PivotTables("PivotTable1") _

If the first item is True, it doesn’t matter if any of the other items are set to True – they will be ignored.

In the screen shot below, I selected several Custom subtotal functions.īut, as soon as I click Automatic, those Custom subtotals are ignored – only the Automatic subtotal will be shown when I click OK. In the manual Field Settings, the Automatic setting is the boss.

Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var, Varp.

The other items in the Array represent the Custom subtotal functions, in the order that they are listed in the Field Settings window.

if you click None, the first item in the Array is False.

if you click Automatic, that item in the Array is True.The first item in the Array is for the Automatic and None settings To see what the macro recorder created, here is one line of the code, for the Category field: ActiveSheet.PivotTables("PivotTable1") _Įach of the items in the Array represents one of the Subtotal options that you see when you right-click on a pivot field, and choose Field Settings. I needed flexible code, for a variety of pivot table layouts, so this wasn’t going to be much help. Instead of just one line of code, a line had been recorded for each field in the source data, to turn off the subtotals individually. Yikes! It didn’t look anything like a simple click of the button had been recorded. In the screen shot below, you can see the code from my macro recording. I turned off the recorder, and opened the Visual Basic Editor.

Then, I recorded the steps while I used the Do Not Show Subtotals command. Ha!įull of optimism, developed over my years of working with Excel, I turned on the macro recorder. Let’s get the code for that, which I’m sure will be equally clean and simple. One click, and the subtotals disappear, or reappear.

Fortunately, there is a handy command for this on the Ribbon, on the Design tab, under PivotTable Tools. This week, I was working on a pivot table macro, and wanted to turn off all the Row Field subtotals. Sometimes the Excel macro recorder creates code that gets you off to a good start.