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 dataPublic 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 aWorksheetEnd Sub' Adapts a pivot table data rangePrivate 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
The opinions expressed herein are my own personal opinions and do not represent
my employer's view in any way.