The original
data source range is going to be replaced, so we discard it at this time.
Now we??™ll assign the CurrentRegion property of cell A1 of the worksheet stored in sData to
the rngData variable:
Set rngData = ??
ActiveWorkbook.Sheets(Left(sData, iWhere - 1)).Cells(1, 1).CurrentRegion
Once we have the CurrentRegion, we can replace the current SourceData value of the
PivotTable object with it:
ActiveSheet.PivotTables("PivotTable1").SourceData = ??
sData & rngData.Address(, , xlR1C1)
We??™re passing in the xlR1C1 enum for the ReferenceStyle argument. This is the string
format the SourceData property is looking for.
Now that we??™ve set the SourceData for the PivotTable to the new CurrentRegion of the data
worksheet, all that??™s left to do is call the Refresh command:
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Let??™s give it a test. On Sheet1, add the following data to the grid for the city of Rochester,
NY, as shown in Figure 6-12.
Figure 6-12. New rows added to PivotTable source data
Open Sheet4 (or the sheet your PivotTable is on, if different). Click any cell inside the
PivotTable. When the PivotTable is selected, a couple of new ribbons are displayed, as shown
in Figure 6-13.
CHAPTER 6 n PIVOTTABLES 237
Figure 6-13. The PivotTable Tools ribbon (Options ribbon shown)
On the PivotTable Tools ribbon, select Options ?¤ Data ?¤ Refresh. Click OK on the
Windows Vista security warning. Nothing happens??”the Rochester data does not display.
Pages:
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232