Excel® 2007 Power Programming with VBA

Chapter 17. Working with Pivot Tables

In This Chapter

Excel’s pivot table feature is, arguably, its most innovative and powerful feature. Pivot tables first appeared in Excel 5, and the feature remains unique to Excel. (No other spreadsheet program has anything that comes close to it.) 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. In this chapter, I cover the following topics:

• What you need to know to create pivot tables with VBA

• Examples of VBA procedures that create pivot tables

• An example of how to use VBA to create a worksheet table from a summary table

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. You also can write VBA code to generate and modify pivot tables.

An Introductory Pivot Table Example

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 month.

Figure 17-1. This simple table is a good candidate for a pivot table.

CD-ROM

This workbook, named simple pivot table.xlsm, is available on ...