VBA - change PivotTable source for every PivotTable within worksheet.

d'Holbach

New Member
Joined
Oct 12, 2013
Messages
47
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:

Code:
For each PivotTable in Worksheet
                PivotTable.DataSource = "new source"
                PivotTable.Refresh
         Next PivotTable
 
Last edited:
Sorry to bring up an older thread. I am trying to do a similar thing here, where I have multiple Pivot tables linked to one cache and I want to update the source based on user input.

Using the code that was posted a few pages ago, I get an error. Here is the exact code in my Excel,

Rich (BB code):
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:
Rich (BB code):
Sub UpdateSourceDataString()
Dim newSourceString As String

    Dim strDbPath As String
    Dim strDbName As String


    strDbPath = Range("cMosesDbPath").Value
    strDbName = Range("cMosesDbFile").Value

    newSourceString = "SELECT *" _
            & " FROM [" & strDbPath & "\" & strDbName & "]"

ChangeCaches newSourceString

End Sub

my Debug.Print sNewSource returns
SELECT * FROM [Y:\Production\PROFIT_TEST_BASIS~MAIN2~EXP.DBF]

When I run this, I get a "reference is not valid" error on "pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=sNewSource, Version:=xlPivotTableVersion14)"

I am using Excel 2010
 
Last edited:
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Your source is not an Excel range so you need to use SourceType:=xlExternal
 
Upvote 0
Your source is not an Excel range so you need to use SourceType:=xlExternal

Thanks for the quick reply. Now I am getting an Application-defined or object-defined error on the same part of the code.

FYI, my Debug.Print sNewSource returns

SELECT * FROM [Y:\Production\PROFIT_TEST_BASIS~MAIN2~EXP.DBF]
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top