PivotTables("PivotTable1").PivotFields("City")
.Orientation = xlRowField
.Position = 2
End With
Notice that its Orientation property is set to xlRowField, denoting row data, and its position
is 2. So in the table??™s rows, we have State in position 1 and City in position 2. If you refer
back to Figure 6-8, you can see the data hierarchy displayed.
Let??™s save this workbook as a macro-enabled workbook. Click the Office button and
choose Save As ?¤ Excel Macro-Enabled Workbook, leaving the name the same (except for the
extension), as shown in Figure 6-10.
Figure 6-10. Saving the file as macro-enabled
Unfortunately, if we rerun theMakePivotTable macro again, we??™ll get an error, as shown in
Figure 6-11.
CHAPTER 6 n PIVOTTABLES 232
Figure 6-11. Running MakePivotTable a second time generates an error.
nNote The runtime error 1004 shown in Figure 6-11 was generated in Windows XP. Windows Vista users
will still see runtime error 1004, but its description will read ???Application-defined or object-defined error.???
We can??™t drop another PivotTable on top of an existing PivotTable. Let??™s make a few
changes to our code to allow us to create our PivotTables dynamically based upon data that
is currently being viewed by the user.
There are two issues that stand out in our existing code:
??? We have to add a new worksheet for an additional PivotTable for the data because we
can??™t use the existing sheet (or we have to find a new location on the existing worksheet).
Pages:
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226