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]

That isn't possible. 1 is the minimum value it could return.

By the way, this:

newSourceString = "'Data'!R1C1:R" & LR & "C1"

only refers to one column. I'd imagine you want more than that for a pivot?

 

[LauraWork]

I am lost then
LR = Cells(Rows.count, 1).End(xlUp).Row definitely returns '0'
the first part returns '1048756' and the 2nd part '-4162' but then it returns 1.

I am really not great with the RC stuff, I've used it a few times before and worked fine but not this time and yes the C should be C25

 

[RoryA]

LR will be 0 while that line is highlighted if you're stepping through the code because the assignment of the value hasn't happened yet. Once you press f8 again, hovering over LR (or looking at the Locals window) should show a value. Assuming your data starts in row 1, I'd generally recommend using this:

With Sheets("Data")
newSourceString = "'" & .Name & "!" & .cells(1).Address(Referencestyle:=xlR1C1)
End With

which will just use the whole block of data starting in A1. To use a different sheet, you only need to change the name in the first line.

 

[LauraWork]

ah thats really cool and much simpler! thanks you

Last question, if I wanted to have a variable on sheet name is that possible? using say ActiveSheet.Name or something?

 

[RoryA]

You could just replace this line:

With Sheets("Data")

with:

With Activesheet

 

[LauraWork]

Sorry but I have tried putting all the code back together and I get a run-time error '5' Invalid procedure call or argument, the code pauses on this part of the changecaches code:

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

 

[RoryA]

What's the value of snewsource at the time?

 

[LauraWork]

ok so figured part of it out - I have amended the code to this now...... it was missing a ' to locate the data tab.

but this now says its only returning R1C1 its not picking up all the data on the data tab which on my first sheet is R631C25

With Sheets("Data")
newSourceString = "'" & .Name & "'" & "!" & .Cells(1).Address(ReferenceStyle:=xlR1C1)

 

[RoryA]

My fault - I forgot the most important bit!

With Sheets("Data")
newSourceString = "'" & .Name & "'!" & .cells(1).Currentregion.Address(Referencestyle:=xlR1C1)
End With

 

[LauraWork]

This now works beautifully!!!! thank you so much for your help