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)).Ra nge(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.