Excel Macro to create a pivot table

Question

I don't have a problem that needs fixing since I just forced-solved it by copying others, but I'm curious how to read this and why it's different than what tutorials show:

When I recorded a macro to create a pivot table, it codes it this way (snip):

ActiveWorkbook.Worksheets("TABXXX").PivotTables("PivotTable9"). _
    PivotCache.CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:= _
    "PivotTable2", DefaultVersion:=7

But everyone says to have this (note that I changed the source and pivot worksheets and calculate the last row # following internet directions):

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "'" & dataWS & "'!A1:K" & lastrow, Version:=7).CreatePivotTable TableDestination:= _
    "'" & pivotWS & "'!R3C1", TableName:="PivotTable1", DefaultVersion:=7

Why does my recorded macro show up the way it does?

Answer 1

In Excel, pivot tables can be built on data that exists in many different places. The data could be on an Excel worksheet, or it could be in a text file, or it could be in a database. As a result, the first step in making a pivot table is to get a copy of the data in a standard format, regardless of where the data comes from. This is what a PivotCache is. So, if you record yourself making the first pivot table from data in Excel, the code will show the creation of the PivotCache object.

ActiveWorkbook.PivotCaches.Create(...

However, when you build a subsequent pivot table on data that already has a pivot cache built, the recorder will identify the existing cache:

ActiveWorkbook.Worksheets("TABXXX").PivotTables("PivotTable9").PivotCache...

Which means, find the PivotCache used for a pivot table named "PivotTable9" on a worksheet named "TABXXX" in the activbe workbook.