We??™re using Excel??™s default location
of row 3/column 1, but you can place the PivotTable anywhere you like on your worksheet.
Finally, add the two commands shown in Listing 6-2 to make the PivotCaches.Create
method and the PivotCache.CreatePivotTable table commands act on our new dynamic
variables.
Listing 6-2. Dynamic PivotTable Creation Code
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= ??
rngRangeToPivot, Version:=xlPivotTableVersion12).CreatePivotTable ??
TableDestination:=sPivotLoc, TableName:="PivotTable1", DefaultVersion ??
:=xlPivotTableVersion12
ws.Select
Compare this to the original version of these lines of code in Listing 6-3.
Listing 6-3. Static Macro Recorder??“Generated PivotTable Creation Code
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= ??
"Sheet1!R1C1:R43C6", Version:=xlPivotTableVersion12).CreatePivotTable ??
TableDestination:="Sheet4!R3C1", TableName:="PivotTable1", DefaultVersion ??
:=xlPivotTableVersion12
Sheets("Sheet4").Select
In the original code, the macro recorder set SourceData:="Sheet1!R1C1:R43C6".We
changed that to refer to the rngRangeToPivot variable, SourceData=rngRangeToPivot. Regardless
of how many rows are in the data range, the data source for our PivotTable will reflect the
correct data.
The next line to compare is our call to the PivotCache object??™s CreatePivotTable method.
The original code set the TableDestination to a location in a hard-coded reference to a worksheet:
CreatePivotTable TableDestination:="Sheet4!R3C1".
Pages:
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228