In this project, I had to work on an Excel file getting its data from a database in order to generate a document usable for the end-user. The problem was that a lot of pivot tables were included everywhere in the document making the manual update of these tables really painful. So, my problem was to automate the process of this update in order to refresh all the pivot tables in all the worksheets. In finally wrote a little piece of code that could be triggered either manually or automatically after an event, for example.
As it could be also useful for you, here is the VBA code :
‘ Scans all the Pivot tables contained in the file and refresh the data
Public Sub RefreshPivotTables()
Dim aWorksheet As Worksheet
Dim aPivotTable As PivotTable
‘ Find the pivot tables in all worksheets
For Each aWorksheet In Worksheets
‘ If a worksheet contains more than one pivot table, refresh all of them
For Each aPivotTable In aWorksheet.PivotTables
‘ Updates also the range of data to be sure that the pivot table uses all the data
‘ If the range of data never changes, it is not necessary to call the sub below.
RefreshPivotTableRange aPivotTable
aPivotTable.PivotCache.Refresh
Next aPivotTable
Next aWorksheet
End Sub
‘ Adapts a pivot table data range
Private Sub RefreshPivotTableRange(aPivotTable As PivotTable)
On Error Resume Next
Dim newRange As Range
Dim WorksheetName As String
‘ Get the worksheet name
WorksheetName = Left(aPivotTable.SourceData, InStr(aPivotTable.SourceData, “!”) – 1)
‘ The GetDataRangeForWorksheet is a sub that returns the new range of data in the given worksheet.
Set newRange = GetDataRangeForWorksheet(Worksheets(WorksheetName))
aPivotTable.SourceData = WorksheetName & “!” & newRange.Address(ReferenceStyle:=xlR1C1)
End Sub
0 Comments