Replace Pivot Table Source Range with New Address VBA

Hi All,

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

Code:

---

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

---

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