PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables ??
("PivotTable1").PivotFields("Qty"), "Sum of Qty", xlSum End Sub
Refreshing Data in an Existing PivotTable Report
How do we handle keeping our data fresh in a PivotTable? When rows are modified, added, or
deleted, how do we pass that on to our PivotTable reports?
If our data had come from an external source like an Access or SQL Server database,
refreshing the data would be as simple as running the following command with the PivotTable
activated:
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
CHAPTER 6 n PIVOTTABLES 235
How do we handle updating our PivotTable data when the data does not sit in a DBMS? If
the data on Sheet1 in our example is modified, how do we refresh the PivotTable?
When we created our macro to build the PivotTable, we assigned a dedicated range of
data to the PivotTable using the ActiveCell.CurrentRegion property. The Refresh command
cannot recalculate the CurrentRegion property we used because it knows nothing about it.
So when we apply the Refresh command, whether through Excel??™s UI or via VBA code, it
only refreshes the data range we initially supplied. Any values that have changed within that
range (or any deleted rows) would be updated, but any additions to the data would not be
applied to the PivotTable.
To update the PivotTable report we created, we will write a subroutine that determines
the original data range of the PivotTable and uses that to recalculate the current data range.
Pages:
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230