- #How to remove subtotals from pivot table macro manual#
- #How to remove subtotals from pivot table macro code#
#How to remove subtotals from pivot table macro code#
The code loops through all the pivot tables on the active sheet. So, I created variables for pivot table and pivot field. To make the code more flexible, I didn’t want to name specific pivot fields – I wanted the code to turn off subtotals on all the fields. PivotFields("Category").Subtotals(1) = False Create Flexible Code Or, turn Automatic subtotals off by setting index 1 to False: ActiveSheet.PivotTables("PivotTable1") _ PivotFields("Category").Subtotals(1) = True The index number for Automatic is 1, so I can turn subtotals on with this line of code: ActiveSheet.PivotTables("PivotTable1") _ If you look in the VBA help for the PivotField.Subtotals property, you’ll see that you can use an index number, instead of an Array. 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.
#How to remove subtotals from pivot table macro manual#
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. The site also contains some other good information for working with PivotTables.Sometimes the Excel macro recorder creates code that gets you off to a good start. The original for this code is available here, at Debra's site: The subtotals for all the fields in the PivotTable are suppressed at once. Just display the PivotTable you want to affect, and then run the macro. (That is what macros are for-to take care of the tedious things you tire of.) Rather than reinvent the wheel, however, a good solution is to consider the following code, adapted from Microsoft MVP Debra Daglisesh's site: The answer is that it would be easier to use a macro. Shairal wondered if it might be easier to use a macro to suppress the subtotals for all the fields at once. This can be time consuming depending on the number of fields he's used. One of the most irritating things he deals with is suppressing the automatic subtotal function on each field, one at a time. Shairal develops PivotTables on a daily basis, using various data sources such as Excel lists, Access tables, and OLAP data.