CODEWITHSUNDEEP
vbaexcelpivot-table
Akire
Akire

Reputation: 169

Error when creating VBA for changing data source in several Pivot Tables

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

Upvotes: 0

Views: 1591

Answers (2)

jeffreyweir
jeffreyweir

Reputation: 4824

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.

Upvotes: 0

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

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

Upvotes: 2

Related Questions