Microsoft Office Tutorials and References
In Depth Information
Understanding PivotCaches
Assuming that the worksheet holding the source data is the active sheet, and that you want the
PivotTable to be located next to the source data, the following single line of code would handle all
those instructions:
ThisWorkbook.PivotCaches.Add _
(SourceType:=xlDatabase, _
SourceData:=Range(“A1”).CurrentRegion).CreatePivotTable _
TableDestination:=”R4C” & Range(“A1”).CurrentRegion.Columns.Count + 2
The notation “R4C” & Range(“A1”).CurrentRegion.Columns.Count + 2 is
translated as the worksheet cell that is on row 4 of the column that is two
columns to the right of the last column in the source range. Recall from earlier in
the lesson that I recommend placing the top-left corner of the PivotTable on row
4, and with an empty column separating the source data and the new PivotTable.
The result you get is a PivotTable, but you’d never know by its appearance at the moment — a
curious range of four cells that look as if they were formatted for thin borders. In this example, the four
cells are in range H4:I5 as shown in Figure 15-20.
figurE 15-20
 
Search JabSto ::




Custom Search