change pivot source using vba won't work

Question

I have been puzzling to have vba change the datasource for multiple pivots. I have been reading many suggestions and none of them have helped me so far. I do not understand what I am doing wrong so I ultimately ask my question here.

Here is what I try to achieve: I have multiple files with pivots that have to be changed because columns have been inserted and my pivot data source has changed. I want to change this to the right range. I created this code:

Sub change_pivotsource()
Dim pt As PivotTable
Dim pts As PivotTables
Dim pt_source As String
Dim ws As Worksheet
Dim wb As Workbook

Set wb = ActiveWorkbook

workbookname = ActiveWorkbook.Name
sheetname = Left(workbookname, Len(workbookname) - 5)
pt_source = "'" & sheetname & "'!$A:$T"


For Each ws In wb.Worksheets
    For Each PivotTable In ws.PivotTables
      pt.SourceData = pt_source
    Next PivotTable
Next ws


End Sub

I am using the workbook name to have the string which is the final datasource location.

How can I have the pt_source as my pivotsource for each pivot in my workbook? Many times I get an "object or block variable with is not set" error

edit 1:

this code give me an error 7 during execution: insufficient memory: and then it highlights the "pt_source ="-row.

Sub change_pivotsource()
Dim ws As Worksheet
Dim wb As Workbook
Dim pt As PivotTable
Dim sheetname As String
Dim pt_source As String
Dim workbookname As String

workbookname = ThisWorkbook.Name
sheetname = Left(workbookname, Len(workbookname) - 5)
pt_source = Sheets(sheetname).Range("$A:$T")

For Each ws In ThisWorkbook.Worksheets
      For Each pt In ws.PivotTables
        pt.ChangePivotCache _
        wb.PivotCaches.Create(SourceType:=xlDatabase, _
              SourceData:=pt_source)

      Next pt
Next ws
'workbook.save
End sub

this code solved my question:

Sub UpdatePivotCaches()
' Updates each pivot cache in the current workbook.
Dim pt As PivotTable        ' Used to loop over each pivot table.
Dim ws As Worksheet         ' Used to loop over each work sheet.
Dim sheetname As String
Dim pt_source As String
Dim workbookname As String

workbookname = ThisWorkbook.Name
sheetname = Left(workbookname, Len(workbookname) - 5)


    ' Loop over each sheet.
    For Each ws In ActiveWorkbook.Sheets

        ' Loop over each pivot table on the sheet.
        For Each pt In ws.PivotTables

            ' Update the pivot cache.
            pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, "'" & sheetname & "'!$A:$T", xlPivotTableVersion14)
        Next
    Next
End Sub

Key to the solution might have been the version parameter (see the comments below this question). Thanks to destination-data

David Rushton · Accepted Answer · 2016-04-12 09:13:22Z

1

Pivot tables get their data from a pivot cache. It is this object we need to change. This procedure replaces the cache connected to each pivot table within the current workbook.

Sub UpdatePivotCaches()
' Updates each pivot cache in the current workbook.
Dim pt As PivotTable        ' Used to loop over each pivot table.
Dim ws As Worksheet         ' Used to loop over each work sheet.


    ' Loop over each sheet.
    For Each ws In ActiveWorkbook.Sheets

        ' Loop over each pivot table on the sheet.
        For Each pt In ws.PivotTables

            ' Update the pivot cache.
            pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create(xlDatabase, "'Sheet1'!C8:D12", xlPivotTableVersion14)
        Next
    Next
End Sub

I've hardcoded the new data source (...'Sheet1'!C8:D12...) but you could improve this procedure by parameterising the range address.

If you ever get stuck with VBA try the macro recorder. This translates your manual actions into VBA. It's a very handy tool.

edited Apr 12, 2016 at 9:13

answered Apr 11, 2016 at 15:21

David Rushton

5,0501 gold badge19 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

DutchArjo Over a year ago

thanks. I tried the macro recorder but this did not return anything for changing my pivot source. I have multiple pivots which all use the same pivot cache. Can I simple change the code to "for each pivottable in worksheet.pivottables?

DutchArjo Over a year ago

My code simply keeps asking for a with argument. None of the website I have read today with solutions have/use a with argument. I do not understand why vba keeps asking me for a with argument. I only want to change all pivot data sources to this range: 'Rustgevers - Values'!$A:$T. But that seems kinda hard to achieve.

David Rushton Over a year ago

My answer is a little brief. I'll update with more detial.

DutchArjo Over a year ago

Thank you. I still do not see what I did wrong, but I used the updated code you have posted, changed it to the datasource I want and the routine works perfect.

David Rushton Over a year ago

Like you I couldn't get pt.SourceData = *New Source* to work. I suspect this is a read only property (I must be honest; I didn't check the documentation). I also had trouble with PivotCaches.Create until I included the version parameter xlPivotTableVersion14.

Collectives™ on Stack Overflow

change pivot source using vba won't work

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related