0

I am trying to create a VBA to change the data source in several Pivot tables in the same datafile and worksheet. The data source is in another workbook and is updated weekly with new data.

"FY 18.xlsb" - Workbook with data

"Data" - Worksheet in "FY 18.xlsb" containing whe data

"Pivot Table" - The Worksheet in the active Workbook where Pivot Tables that should be updated are found.

Either I want the VBA to find the end point in the data-worksheet or to have a pop-up window where I can choose the datarange.

Get stopped when running Debug on the code at "pt.ChangePivotCache pc"

Anyone having any ideas what can be wrong? Or any ideas how the code can be updated?

Sub AdjustPivotDataRange()
Dim pt As PivotTable, pc As PivotCache
Dim dataSheet As Worksheet, ws As Worksheet, wks As Worksheet
Dim StartPoint As Range, dataSource As Range, NewRange As String

' Datasource workbook and worksheet
Set wkb = Workbooks("FY 18.xlsb")
Set wks = wkb.Worksheets("Data")

' Worksheet
Set ws = ActiveWorkbook.Worksheets("Pivot Table")

' Dynamically retrieve range address of data
Set StartPoint = wks.Range("A1")
Set dataSource = wks.Range(StartPoint, StartPoint.SpecialCells(xlLastCell))
NewRange = wks.Name & "!" & dataSource.Address(ReferenceStyle:=xlR1C1)

' Create new PivotCache
Set pc = ThisWorkbook.PivotCaches.Create( _
           SourceType:=xlDatabase, _
           SourceData:=NewRange)

' Loop through all tables in worksheet
For Each pt In ws.PivotTables

        ' update pivot source and refresh
        pt.ChangePivotCache pc
        pt.RefreshTable

Next pt 
End Sub

EDIT: New code that does what I want!

Sub AdjustPivotDataRange()
Dim pt As PivotTable, pc As PivotCache
Dim dataSheet As Worksheet, ws As Worksheet, wks As Worksheet
Dim StartPoint As Range, dataSource As Range, NewRange As String

' Datasource workbook and worksheet
Set wkb = Workbooks("Nordics SQI - FY 19.xlsb")
Set wks = wkb.Worksheets("Data")

' Worksheet
Set ws = ActiveWorkbook.Worksheets("Pivot Table")

' Dynamically retrieve range address of data
 Set dataSource = wks.Range("A1").CurrentRegion

' Loop through all tables in worksheet
For Each pt In ws.PivotTables

        ' update pivot source and refresh
        pt.ChangePivotCache ThisWorkbook.PivotCaches.Create( _
           SourceType:=xlDatabase, _
           SourceData:=dataSource)
        pt.RefreshTable

Next pt

End Sub
Improve this question
3
  • If all these Pivots share the exact same data source, they all share the same PivotCache too. So you only need to refresh the cache once. In addition, if your data source is an Excel Table your PivotCache should pick up any new data on refresh. Meaning you probably don't need any of this code. Commented May 11, 2018 at 20:56
  • I've had some problems with this. But will have another look. Commented May 14, 2018 at 8:05
  • Tried it, worked one time, today when I should update the data it does not work with having the Excel Table, I need to choose range.. Though, I have added a slicer, can this make a different? Commented May 28, 2018 at 12:04

2 Answers 2

Reset to default
2

First idea- PivotCache data source should be just a continuous range therefore try with this:

'Set StartPoint = wks.Range("A1") 'not required
Set dataSource = wks.Range("A1").currentregion
'NewRange = wks.Name & "!" & dataSource.Address(ReferenceStyle:=xlR1C1) 'not required

' Create new PivotCache
Set pc = ThisWorkbook.PivotCaches.Create( _
           SourceType:=xlDatabase, _
           SourceData:=dataSource) '!!

EDIT in reference to edited question:

I'm not sure if this is efficient to create new PivotCache in the lop for each individual PivotTable. Try with this solution being a combination of my first answer and you proposed solution:

Not tested

Dim newPC as PivotCache
Set newPC = ThisWorkbook.PivotCaches.Create( _
           SourceType:=xlDatabase, _
           SourceData:=dataSource)

For Each pt In ws.PivotTables

        ' update pivot source and refresh
        pt.ChangePivotCache newPC
        pt.RefreshTable

Next pt
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

It seems to be working! Will come back when I have done some tests on it! THANKS!
It still wants to debug "pt.ChangePivotCache pc", though when running it, one of my two Pivot Tables are updeted...
It runs the code one time, but when it should update Pivot table number 2 it gets stuck.
the question is if both previous and new PivotCache range covers the same set of Fields? something could be missing and something could be different
Old range was Data'!$A$1:$X$153695 new range Data'!$A$1:$X$209475. Both had the same range from the beggining.
|
0

No VBA neccessary. Just change your source range to an Excel Table, and repoint your PivotTables at that Table, because Tables are dynamic Named Ranges. From that point on, your Table will grow automatically to accommodate new data, and any time you refresh just one PivotTable they all will be refreshed as they all share the same PivotCache.

Improve this answer

1 Comment

Already have the range as an Excel Table in the source Workbook, but it does not want to copy in as something other than a range, but will have a try again.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.