Excel 2007 - Modifying PivotTable Source Data

[PureSlate]

Hi all,

I'm having an issue correctly modifying the source data for a given pivot table.


I currently have the following code:

ActiveSheet.PivotTables("PivotTable3").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "ETD WIP DATA!R10C1:R" & LastRow & "C23" _
        , Version:=xlPivotTableVersion12)

The issue I believe I am having is passing the string to "SourceData". I have attempted removing the [" & LastRow & "] portion of the code, and replacing it with a static number, but I still end up with a runtime 80070057 error (PivotTable field name is not valid. I have double checked, and none of the field names have changed, and I have used the same dummy address for the code that I have manually set before.

Can anyone help me out? Thanks!

 

[PureSlate]

While I am still interested in figuring out how to do this, I found a workaround for my specific set. I set the existing pivot tables to a named range data source, and set the named range within the VBA code itself. While this is not a desired solution, it appears to work the same in the end.

 

[SkipVought]

Hi,

ActiveSheet.PivotTables("PivotTable3").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "'ETD WIP DATA'!R10C1:R" & LastRow & "C23" _
        , Version:=xlPivotTableVersion12)

Your sheet name has SPACES and therefore needs SINGLE QUOTES around it.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]