Changing data source of pivot tables with VBA

I'm looking for a code that can change the data source of my pivot tables from one sheet with a different data source. The data source sheet is identical just with different data.

I have a code that i've taken from the internet, but instead of changing the data source of my active sheet, it changes the data source on all my sheets.

Sadly i don't know much about VBA coding to know how to change the code to affect only my active sheet and i was hoping someone could help me.

Option Explicit
Sub ChangeDataSourceForAllPivotTables() Dim wb As Workbook Dim ws As Worksheet Dim pt As PivotTable Dim rSourceData As Range If ActiveWorkbook Is Nothing Then Exit Sub On Error GoTo ErrHandler Set wb = ActiveWorkbook Set rSourceData = wb.Worksheets("Sheet1").Range("A1").CurrentRegion 'change the name of the worksheet accordingly For Each ws In wb.Worksheets For Each pt In ws.PivotTables pt.ChangePivotCache wb.PivotCaches.Create(xlDatabase, rSourceData.Address(, , , True)) pt.RefreshTable Next pt Next ws
ExitTheSub: Set wb = Nothing Set ws = Nothing Set pt = Nothing Set rSourceData = Nothing Exit Sub
ErrHandler: MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error" Resume ExitTheSub
End Sub

Thank you in advance for the help!

1 Answer

This code will help you to change Source Data for all Pivot Table in particular Sheet.

Sub ChangePivotSourceData()
Dim pt As PivotTable
For Each pt In ActiveWorkbook.Worksheets("Sheet1").PivotTables pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:="MyData")
Next pt
End Sub

How it works:

• Copy & Paste this Code as Standard Module.
• Sheet and Source Data names are editable.
• In this code MyData is a Named Range, that you need to create before you RUN this Code every time you want to change Source Data for Pivot Table.