VBA - change PivotTable source for every PivotTable within worksheet.

[d'Holbach]

I have a rather complex report which is based on data it references from specifically set out and formatted 9 PivotTables located on a seperate worksheet. The PivotTables pull data from an Excel spreadsheet (specific range) which is located on a network drive. At the moment whenever I want to run the report with new version of data I'm manually updating data sources of all PivotTables to refresh the report by selecing PivotTable and going Change Data Source and selecting a range from external worksheet and repeating this action for all PivotTables.

The connection string I use in the Change Data Source looks like this:
"K:\FolderA\FolderB\FolderC\2013-14\\[nameOfTheFile.xlsx]Data 13-14'!$A$1:$GM$130253"

This is a pseudocode of what I would like to achieve:

For each PivotTable in Worksheet
                PivotTable.DataSource = "new source"
                PivotTable.Refresh
         Next PivotTable

 

[RoryA]

I think the issue is probably with multiple pivots off one cache. Perhaps try this:

Sub ChangeCaches(sNewSource As String)
   Dim pc                          As PivotCache
   Dim ws                          As Worksheet
   Dim pt                          As PivotTable
   Dim bCreated                    As Boolean
   
   For Each ws In ActiveWorkbook.Worksheets
      For Each pt In ws.PivotTables
         If Not bCreated Then
            pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                        SourceData:=sNewSource, Version:=xlPivotTableVersion14)
            Set pc = pt.PivotCache
            bCreated = True
         Else
            If pt.CacheIndex <> pc.Index Then pt.CacheIndex = pc.Index
         End If
      Next pt
   Next ws
End Sub

Called like this:

Sub UpdateSourceDataString()
Dim newSourceString As String

newSourceString = "'[PivotSource.xlsx]Sheet1'!R1C1"

ChangeCaches newSourceString

End Sub

 

[d'Holbach]

Thank you, that looks to be working perfectly. There are two errors in the code:

1)

SourceData:=sNewSourceFile

should read:

SourceData:=sNewSource

2)

ChangeCaches newSourceString

should read

ChangeCaches (newSourceString)

after making those two changes code executes fine. Thank you to all who responded.

 

[RoryA]

Thanks - I've adjusted the typo in the code and changed the variable declaration to String rather than Variant, since I prefer:

Dim newSourceString As String

newSourceString = "'[PivotSource.xlsx]Sheet1'!R1C1"

ChangeCaches newSourceString

 

[Andrew Poulsom]

I think the issue is probably with multiple pivots off one cache.

Why on earth does that make a difference?

 

[RoryA]

Honestly, I have no idea - I remembered having a similar issue in Excel 2003 and prior with multiple pivots attached to one cache using an external ODBC query (which you could work around by switching to OLEDB) and wondered if it might be a similar problem here.

I'm also not really sure why this works:

pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                        SourceData:=sNewSource, Version:=xlPivotTableVersion14)

whereas you can't create the cache first and store it in a variable, then assign it using the same method.

 

[Andrew Poulsom]

The write up on the ChangePivotCache property is nonsense:

PivotTable.ChangePivotCache Method (Excel)

How can a string be "A PivotTable or PivotCache object that represents the new PivotCache for the specfied (sic) PivotTable."?

 

[RoryA]

Just when you think the Help can't get worse...

 

[LauraWork]

Hi there

I know this post is old but it works great so thank you!!!

the only issue I have is I don't always know how many rows my source data is, I have tried to create a 'last row' lookup but that doensn't work.

where have I gone wrong?


Dim LR As Long


Dim newSourceString As String
LR = Cells(Rows.count, 1).End(xlUp).Row


newSourceString = "'Data'!R1C1:R" & LR & "C1,RC[-1]"

 

[RoryA]

What is the RC[-1] on the end supposed to be doing?

 

[LauraWork]

good spot! absolutely no idea! copied it from VBA I use for something else so not needed.

I now just have
newSourceString = "'Data'!R1C1:R" & LR & "C1"

I have re run and this bit is returning '0'
LR = Cells(Rows.count, 1).End(xlUp).Row