Last year, I posted Excel VBA code for removing a calculated field from a pivot table. The code works well if there is only one pivot table based on that pivot cache. However, a couple of comments mentioned that there were problems if multiple pivot tables shared the pivot cache. Here is a revised version [...]

It’s easy to change the summary function for one data field in a pivot table. For example, if a field shows the Sum of Units, you can right-click on that field, and change it to Count of Units. However, if you have several data fields in a pivot table, it can take quite a bit [...]

When you double-click on the value cell in a pivot table, a new sheet is automatically inserted in the workbook. The new sheet contains a table, listing all the source data records that are summarized in the number that you double-clicked. Double-clicking the cell is a shortcut to the pivot table Show Details command. You [...]

Pivot table macros can run painfully slowly, even if you have turned off screen updating and disabled events. To make the code run faster, you can try adding a line that turns off automatic updating in the pivot table. For example: Set pt = Worksheets("PivotSales").PivotTables(1) pt.ManualUpdate = True At the end of the code, you [...]

If you’re working with pivot table calculated fields in Excel VBA programming, or trying to use the Orientation property for data fields, you might encounter some problems. Here’s how I managed to work around an error that occurs when you try to remove a calculated field from the pivot table layout with Excel VBA. Strange [...]

To create a pivot table from data on different worksheets, you can use a Multiple Consolidation Ranges pivot table, but the result is a pivot table with limited features and functionality. Previously, Excel MVP Kirill Lapin shared his macro for creating a standard pivot table from data on different worksheets. Now Kirill has expanded that [...]

If you have an Excel workbook that contains several pivot tables and multiple Excel Tables, you might want to identify which pivot cache each pivot table uses. To figure out which pivot cache a pivot table uses, you can use a macro to check the pivot table’s CacheIndex property and show the index number in [...]