Incorporate data source update of all pivot tables before slicers are reconnected|VBA & Macros|Excel Forum|My Online Training Hub

Hi Catalin,

Thank you very much for sharing the disconnect and connect codes.

Do you know how to incorporate data source update of all pivot tables before slicers are reconnected?

I tried pasting the following code fould online before reconnection code but the reconnection doesn't work anymore. I guess it need to have variables labels aligned with your codes' declarations but not sure how to do it.

If you had a chance, please can you advise?

Sub AdjustAllPivotDataRanges()
'PURPOSE: Dynamically change every pivot table's data source range in the workbook
'SOURCE: TheSpreadsheetGuru.com/the-code-vault

Dim sht As Worksheet
Dim pvt As PivotTable
Dim StartPoint As Range
Dim rng As Range
Dim SourceAddress As String

'Enter Worksheet Name that holds your Pivot data source
Set sht = ActiveWorkbook.Worksheets("DealsDataSource")

'Enter first cell in your Pivot data source
Set StartPoint = sht.Range("A1")

'Create SourceData address
Set rng = sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))
SourceAddress = sht.Name & "!" & rng.Address(ReferenceStyle:=xlR1C1)

'Loop through and update pivot tables with new data source range
For Each sht In ThisWorkbook.Worksheets
For Each pvt In sht.PivotTables

'Change Pivot Table's data source range address
pvt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SourceAddress)

'Ensure Pivot Table is refreshed
pvt.RefreshTable

Next pvt
Next sht

'Completion Message
MsgBox "All Pivot Table Data Source Ranges have been updated in this workbook!", vbInformation

End Sub