We replaced that with a call to
our dynamic variable sPivotLoc, which refers to the name of the new worksheet we added,
whatever that might be: CreatePivotTable TableDestination:=sPivotLoc.
CHAPTER 6 n PIVOTTABLES 234
The last difference is that the original code selects the hard-coded worksheet,
Sheets("Sheet4").Select, while our new dynamic code simply refers to the ws variable and
selects the worksheet it contains using the Worksheet object??™s Select method, ws.Select.
Listing 6-4 shows the completed MakeDynamicPivotTable subroutine.
Listing 6-4. Complete MakeDynamicPivotTable Subroutine
Sub MakeDynamicPivotTable()
Dim ws As Worksheet
Dim rngRangeToPivot As Range
Dim sPivotLoc As String 'where to place the PivotTable on the new sheet
Set rngRangeToPivot = ActiveCell.CurrentRegion
Set ws = Sheets.Add
sPivotLoc = ws.Name & "!R3C1"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= ??
rngRangeToPivot, Version:=xlPivotTableVersion12).CreatePivotTable ??
TableDestination:=sPivotLoc, TableName:="PivotTable1", DefaultVersion ??
:=xlPivotTableVersion12
ws.Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("State")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("City")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.
Pages:
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229