PivotTable Save Time with a Macro

What if you want to have a macro perform all those steps for you, creating a PivotTable from any column you feed it? If you simply record a macro, you'll find it often works only if your data has the same heading. To avoid this, you can create a simple macro stored in your workbook or in your personal macro workbook (described in Chapter 7), and use it to create a PivotTable on any list of items. This requires that you write some generic VBA code and enter it into a standard module in your personal macro workbook or in any other workbook.

To start, right-click on the sheet tab that contains the data table and select ViewCode (on Mac, Alt/Option-F11; in pre-2007 versions, Tools → Macro → Visual Basic Editor). Then, choose Insert → Module and enter the following code:

    Sub GetCount( )
    Dim Pt As PivotTable
    Dim strField As String

     strField = Selection.Cells(1,1).Text
     Range(Selection, Selection.End(xlDown)).Name = "Items"

     ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
     SourceData:="=Items").CreatePivotTable TableDestination:="", _
     TableName:="ItemList"

     Set Pt = ActiveSheet.PivotTables("ItemList")
     ActiveSheet.PivotTableWizard TableDestination:=Cells(3, 1)
     Pt.AddFields RowFields:=strField
     Pt.PivotFields(strField).Orientation = xlDataField

    End Sub

To return to Excel, close the Script window (or press Alt-Q) and save your workbook.

Before running this code, select the heading of your list and ensure that your list contains no blank cells.

Sorting your list will remove blank cells quickly, because blank cells are always sorted to the bottom of a list.

The code will automatically create a named range of your list, called Items. It will then create the PivotTable based on this named range on a new worksheet. The next time you have a long list of data, you can simply select its heading and run this macro. All the PivotTable setup work will be done in the blink of an eye.