Refreshing Data in an Existing Pivot Table 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

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. It will then apply that data range to the PivotTable's SourceData property, and then refresh the PivotTable.

In the VBE, create a new subroutine and name it RefreshPivotTableFromWorksheet. Add the following code:

Sub RefreshPivotTableFromWorksheet() Dim sData As String Dim iWhere As Integer Dim rngData As Range sData = ActiveSheet.PivotTables("PivotTable1").SourceData iWhere = InStr(1, sData, "!") sData = Left(sData, iWhere)

Set rngData =

ActiveWorkbook.Sheets(Left(sData, iWhere - 1)).Cells(1, 1).CurrentRegion

ActiveSheet.PivotTables("PivotTable1").SourceData =

sData & rngData.Address(, , xlR1C1)

End Sub

Let's take a look at what this code is doing. We have three variables declared. sData will hold the value of the current range for the PivotTable's source data. We want to find the bang character (!) so we can retrieve the name of the worksheet the data came from. We'll store that in the iWhere variable. And finally, we have a variable of type Range, rngData, that will be assigned the CurrentRegion of cell A1 on the data worksheet. With this information, we have the tools to refresh our pivot data any time detail data is added on the data worksheet.

The first step is to get the current data source for the PivotTable:

sData = ActiveSheet.PivotTables("PivotTable1").SourceData

Next we'll find the ! character: iWhere = InStr(1, sData, "!")

Now we want the worksheet name including the ! :

sData = Left(sData, iWhere)

We modify sData because we only needed it to determine the worksheet name. 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 Sheetl, 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.

Figure 6-13. The PivotTable Tools ribbon (Options ribbon shown)

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.

On the Developer ribbon, run the RefreshPivotTableFromWorksheet subroutine. Now the new city appears in the data summary, as shown in Figure 6-14.

Figure 6-14. Rochester data displayed after RefreshPivotTableFromWorksheet is run
0 0

Post a comment