How to change pivot table data source

Help Activities
,
1

Hi all! I want to change my excel’s data source from another excel.

I used this vba code. But i am getting an error. Please help me.

Sub ChangePivotDataSource()
    Dim pt As PivotTable
    Dim newDataSource As String
    Dim filePath As String

    filePath = "C:\Users\....\PL ULF Y23 W07.xlsx"
    newDataSource = "'PL ULF'!A1:GU100000"
    
    ThisWorkbook.Activate
    

    Workbooks.Open(filePath).Activate
    Set pt = ActiveSheet.PivotTables("PivotTable2")
    pt.ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=newDataSource)
End Sub

MicrosoftTeams-image (2)

3

Hi @Betul_Dundar

Please try the following code & let us know:

Sub ChangePivotDataSource()
Dim pt As PivotTable
Dim newDataSource As String
Dim filePath As String
Dim wb As Workbook
Dim ws As Worksheet
filePath = "C:\Users\....\PL ULF Y23 W07.xlsx"
newDataSource = "'PL ULF'!A1:GU100000"
Set wb = Workbooks.Open(filePath)
Set ws = wb.Sheets("Sheet1") 
Set pt = ws.PivotTables("PivotTable2") 
pt.ChangePivotCache wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=newDataSource)
wb.Close SaveChanges:=True 
End Sub

Hope this helps,
Best Regards.

4

Thanks @arjunshenoy , but it gives me an error:

MicrosoftTeams-image (3)

My source sheet name is PL ULF , my pivot table sheet name is SALES PERFORMANCE.

Please check:

Sub ChangePivotDataSource()
Dim pt As PivotTable
Dim newDataSource As String
Dim filePath As String
Dim wb As Workbook
Dim ws As Worksheet
filePath = "C:\Users\...\PL ULF Y23 W07.xlsx"
newDataSource = "'PL ULF'!A1:GU100000"
Set wb = Workbooks.Open(filePath)
Set ws = wb.Sheets("PL ULF") 
Set pt = ws.PivotTables("PivotTable2") 
pt.ChangePivotCache wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=newDataSource)
wb.Close SaveChanges:=True 
End Sub
5

@Betul_Dundar

Please try this one:

Sub ChangePivotDataSource()
Dim pt As PivotTable
Dim newDataSource As String
Dim filePath As String
Dim wb As Workbook
Dim ws As Worksheet
filePath = "C:\Users\....\PL ULF Y23 W07.xlsx"
newDataSource = "'PL ULF'!A1:GU100000"
Set wb = Workbooks.Open(filePath)
Set ws = wb.Sheets("SALES PERFORMANCE")
Set pt = ws.PivotTables("PivotTable2")
pt.ChangePivotCache wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=newDataSource)
wb.Close SaveChanges:=True 
End Sub

Hope this helps,
Best Regards.

6

I tried but gives me an error:

Subscript out of range

7

Hi all! I found a code, write code in txt . Using invoke VBA activity :

Sub ChangePivotTableSource(sheetName As String, pivotName As String, newSource As String)
    Dim pt As PivotTable
   
 
    Set pt = ThisWorkbook.Sheets(sheetName).PivotTables(pivotName)
    pt.ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=newSource)
End Sub

Code File Path: “changePivotSource.txt”
Entry parameters: {“SALES PERFORMANCE”,“PivotTable2”,“‘in_path[filename]PL ULF’!$A$1:$GU$100000”}
Entry Methods: “ChangePivotTableSource”

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.