Replace Pivot Table Source Range with New Address VBA

[Admin]

Hi All,

Here is sub routine which enable you to replace the existing source range with new address in VBA.

Sub ReplacePivotSourceData(ByVal WorkbookName As String, ByVal PivotSheetName As String, _
                            ByVal PivotTableName As String, ByVal SourceDataSheetName As String, _
                                                            ByVal NewPivotSourceRange As String)
    
    '// Author      : Kris @ ExcelFox
    
    Dim wbkActive   As Workbook
    Dim ptPivot     As PivotTable
    Dim ptCache     As PivotCache
    Dim wksPivot    As Worksheet
    
    Set wbkActive = Workbooks(CStr(WorkbookName))
    Set wksPivot = wbkActive.Worksheets(CStr(PivotSheetName))
    
    Set ptPivot = wksPivot.PivotTables(CStr(PivotTableName))
    
    Set ptCache = wbkActive.PivotCaches.Create(1, wbkActive.Worksheets(CStr(SourceDataSheetName)).Range(CStr(NewPivotSourceRange)).Address(external:=1))
    
    ptPivot.ChangePivotCache ptCache
    ptPivot.PivotCache.Refresh
    
    Set wbkActive = Nothing
    Set ptPivot = Nothing
    Set ptCache = Nothing
    Set wksPivot = Nothing
    
End Sub

[rafal1122]

Thank you. After trying dozens of various solutions found online, only this one works perfectly.