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.<\/FONT><\/P> As it could be also useful for you, here is the VBA code :<\/P> ‘ Scans all the Pivot tables contained in the file and refresh the data<\/SPAN> ‘ Find the pivot tables in all worksheets<\/SPAN> ‘ Get the worksheet name<\/SPAN> How to refresh automatically all the PivotTables contained in an Excel Workbook…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-1889","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.peneveyre.com\/en\/wp-json\/wp\/v2\/posts\/1889","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.peneveyre.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.peneveyre.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.peneveyre.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.peneveyre.com\/en\/wp-json\/wp\/v2\/comments?post=1889"}],"version-history":[{"count":0,"href":"https:\/\/www.peneveyre.com\/en\/wp-json\/wp\/v2\/posts\/1889\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.peneveyre.com\/en\/wp-json\/wp\/v2\/media?parent=1889"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.peneveyre.com\/en\/wp-json\/wp\/v2\/categories?post=1889"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.peneveyre.com\/en\/wp-json\/wp\/v2\/tags?post=1889"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n
\n
Public<\/SPAN> Sub<\/SPAN> RefreshPivotTables()
Dim<\/SPAN> aWorksheet As<\/SPAN> Worksheet
Dim<\/SPAN> aPivotTable As<\/SPAN> PivotTable
<\/SPAN><\/P>
\n
For<\/SPAN> Each<\/SPAN> aWorksheet In<\/SPAN> Worksheets
‘ If a worksheet contains more than one pivot table, refresh all of them<\/SPAN>
For<\/SPAN> Each<\/SPAN> aPivotTable In<\/SPAN> aWorksheet.PivotTables
‘ Updates also the range of data to be sure that the pivot table uses all the data<\/SPAN>
‘ If<\/SPAN> the range of data never changes, it is<\/SPAN> not<\/SPAN> necessary to<\/SPAN> call<\/SPAN> the sub<\/SPAN> below.
<\/FONT> RefreshPivotTableRange aPivotTable
aPivotTable.PivotCache.Refresh
Next<\/SPAN> aPivotTable
Next<\/SPAN> aWorksheet
End<\/SPAN> Sub<\/SPAN>
‘ Adapts a pivot table data range<\/SPAN>
Private<\/SPAN> Sub<\/SPAN> RefreshPivotTableRange(aPivotTable As<\/SPAN> PivotTable)
On<\/SPAN> Error<\/SPAN> Resume<\/SPAN> Next<\/SPAN>
Dim<\/SPAN> newRange As<\/SPAN> Range
Dim<\/SPAN> WorksheetName As<\/SPAN> String<\/SPAN>
<\/SPAN><\/SPAN><\/SPAN><\/P>
\n
WorksheetName =<\/SPAN> Left<\/SPAN>(aPivotTable.SourceData, InStr<\/SPAN>(aPivotTable.SourceData, “!”<\/SPAN>) –<\/SPAN> 1)
‘ The GetDataRangeForWorksheet is<\/FONT><\/SPAN> a sub<\/FONT><\/SPAN> that returns the new<\/FONT><\/SPAN> range of data in<\/FONT><\/SPAN> the given worksheet.
<\/FONT> Set<\/SPAN> newRange =<\/SPAN> GetDataRangeForWorksheet(Worksheets(WorksheetName))
aPivotTable.SourceData =<\/SPAN> WorksheetName & “!”<\/SPAN> & newRange.Address(ReferenceStyle:=xlR1C1)
End<\/SPAN> Sub<\/SPAN>
<\/P><\/SPAN><\/FONT><\/p>\n","protected":false},"excerpt":{"rendered":"