101 Ready-To-Use Excel Macros

Excel offers a fairly robust object model for PivotTables. You can use the macro recorder to create a macro that does just about anything with a PivotTable, and the macro gets you 90 percent of the way to automation. For instance, you can record a macro that builds a PivotTable, and that macro records your steps and duplicates your tasks with relatively high fidelity. So if you find yourself needing to automate tasks like filtering out the top 10 items or grouping data items, you can reliably turn to the macro recorder to help write the VBA needed.

That being said, certain PivotTable-related tasks are not easily solved with the macro recorder. This is what this Part focuses on. Here, we cover the most common scenarios where macros help you gain efficiencies when working with PivotTables.

The code for this Part can be found on this book's companion website. See this book's Introduction for more on the companion website.

Macro 62: Create a Backwards-Compatible PivotTable

If you are still using Excel 2003, you may know about the compatibility headaches that come with PivotTables between Excel 2003 and later versions. As you can imagine, the extraordinary increases in PivotTable limitations lead to some serious compatibility questions. For instance, later versions of Excel PivotTables can have more than 16,384 column fields and more than 1,000,000 unique data items. Excel 2003 can ...