iphonejnr.blogg.se

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.