Microsoft Office Tutorials and References
In Depth Information
Chapter 17: Working with Pivot Tables
Working with Pivot Tables
In This Chapter
● Creating pivot tables with VBA
● Looking at examples of VBA procedures that create pivot tables
● Using VBA to create a worksheet table from a summary table
An Introductory Pivot Table Example
Excel’s pivot table feature is, arguably, its most innovative and powerful feature. Pivot tables first
appeared in Excel 5, and the feature has been improved in every subsequent version. This
chapter is not an introduction to pivot tables. I assume that you’re familiar with this feature and its
terminology and that you know how to create and modify pivot tables manually.
As you probably know, creating a pivot table from a database or list enables you to summarize
data in ways that otherwise would not be possible — and it’s amazingly fast and requires no
formulas. You also can write VBA code to generate and modify pivot tables.
This section gets the ball rolling with a simple example of using VBA to create a pivot table.
Figure 17-1 shows a very simple worksheet range. It contains four fields: SalesRep, Region, Month,
and Sales. Each record describes the sales for a particular sales representative in a particular
This workbook, named simple pivot table.xlsm , is available on the companion