Update Pivot Table Source VBA

Question

I want to update the Data Source of a pivot table but when I run the makro I get following error:

The PivotTable field is not a valid. To create a Pivottable report, you must use data that is organized as a list with labeled columns...

DataArea = "Log Defects!$A$3:$L$10000" ActiveSheet.PivotTables("PivotTablePriority").SourceData = DataArea

The Pivot Table name is correct! The Data Area is copyied from excel (when I click on the pivot table and can select the data source) so the selection should be correct.

What do you exactly mean with headers? Just the first row containing the headlines of the column? — Michael
Hey @Michael, I think your understanding of comment is correct... Headers being the first row of a data "block" containing the column names. Are those names in row 3 of the 'Log Defects' sheet? — Dan Wagner
AND typing that last comment might have given me a lead on the problem. It seems you don't have single quotes around Log Defects... When I make a pivot table on my local computer, if the data is on a sheet that has spaces in the name I need single quotes, meaning that line should say DataArea = "'Log Defects'!$A$3:$L$10000" — Dan Wagner

Tim Swingle Tim Swingle · Accepted Answer · 2015-07-14T15:15:18

        Dim WSname as String
        WSname= "Log Defects"
        ActiveSheet.PivotTables("PivotTablePriority").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        WSname & "!R3C1:R10000C12", Version:= _
        xlPivotTableVersion14)

Note WSname is in no quotes. also to change the scaling R=row so its row 3 to 10000 and C= column so 1 to 12 or column A to L.

Update Pivot Table Source VBA

1 Answers