Pivot Tables are a very popular and powerful Excel feature and most users generate this type of object using the standard wizard (pre 2007) or Insert action (2007 or later) command.

Once again, recording a macro is a good starting point but the code, efficiency and interpretation is sometimes difficult to manage and can cause errors when running a recorded macro. Instead, users can always call and Excel Pivot Tables object which is a member of the Pivot Tables Collection.

Here is an example recorded macro based on some data located in a worksheet called 'Sales List' which has a range A1:M306:

Sub SummaryPivotReport()

    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:="Sales List!R1C1:R306C13", _
        Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", _
        TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Assistant")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField _
        ActiveSheet.PivotTables("PivotTable1").PivotFields("TOTAL"),
        "Sum of TOTAL", xlSum
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Method")
        .Orientation = xlPageField
        .Position = 1
    End With

End Sub

Try running it and you will discover one of several errors.

The errors generated is not down to the Pivot Table object, PivotCaches or PivotTables collection failing but the absolute references to either a worksheet or ranges being called.

Even if you are prepared to keep the recorded macro as above and just simply change the references, then you have made a start and a reason for editing this macro.

For example, changing the range reference (which is absolute) and handling the absolute worksheet name to be more dynamic and relative:

When the system adds a new worksheet Sheets.Add it generates a unique name each time (which is absolute). Later in the procedure it refers to the name of new added worksheet (which is why it fails when running the macro).

Instead of referring to TableDestination:="Sheet1!R3C1" in the Pivot objects TableDestination argument, consider using this code TableDestination:=ActiveSheet.Cells(1, 1)instead which picks the current worksheets cell A1.

Remove the line Sheets("Sheet1").Select and the rest of the code should now work as recorded.

Now lets take a look at the Pivot Table object itself and build the knowledge so that you understand the elements and arguments correctly.

Use the Create and CreatePivotTable methods of the PivotCaches object:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:="Sales List!R1C1:R306C13", _
        Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:=ActiveSheet.Cells(1, 1), _
        TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

Note: In Excel 2003 (or earlier) users tend to use the Add method instead of the Create method which has its own set of arguments.

The SourceData argument is a range reference to the data list source. Also note this reference in the above example is an absolute reference too and should be careful should the data source change and grow dynamically. Consider using a variable or object variable to hold the current address of a region of data to pass into the Create method.

For example (before adding the new worksheet - Sheets.Add) include the following:

Selection.CurrentRegion.Select
MyDataRef = Selection.Address

Or, consider an object reference instead:

Set rngSource As ActiveSheet.Range("A1").CurrentRegion

The TableDestination argument pinpoints where the starting cell in a worksheet (normally a new worksheet) is and should really be dynamic and relative (as previously mentioned).

The remaining arguments are optional but as the macro records distinct settings they have been included. Refer to the Excel VBA help files for further information.

Pivot Tables are objects and can given a unique name as in the above example shown called "PivotTable1".

Using the named object, you can then refer to elements in a Pivot Table which include PivotFields collections and their properties; Orientation, Position and calculated functions.

Here is a revised piece of code for the same above procedure:

Sub SummaryPivotReport()

    Dim rngSource As Range
    Dim wksTrans As Worksheet
    Dim ptTrans As PivotTable

    'Create and set objects
    Set rngSource = ActiveSheet.Range("A1").CurrentRegion
    Set wksTrans = Worksheets.Add(after:=ActiveSheet)

    'Generate a new Pivot Table report using the above objects
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=rngSource, _
        Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:=wksTrans.Range("A1"), TableName:="Trans", _
        DefaultVersion:=xlPivotTableVersion10

    'set an instance for the new pivit table
    Set ptTrans = wksTrans.PivotTables("Trans")

    'set and add fields to the new pivot table (Trans)
    ptTrans.AddFields RowFields:="Product", ColumnFields:="Assistant"

    'Adding the sum function to the main data section
    ptTrans.PivotFields("TOTAL").Orientation = xlDataField
    ptTrans.DataFields(1).Function = xlSum
    ptTrans.DataFields(1).Name = "Total Sales"

End Sub

The above example will give you better control and more flexibility in defining a Pivot Table without have to ensure your have added a new worksheet, placed the cursor in the right position and any duplicate name references (worksheets).

Next Topic: Formulas

Want to teach yourself Access? Free online guide at About Access Databases

VBA Keywords: PivotCaches, CreatePivotTable, Sheets, Cells, Select, ActiveSheet, Dim, Set & CurrentRegion.