CODEWITHSUNDEEP
vbaexcel
Michael Brenndoerfer
Michael Brenndoerfer

Reputation: 4066

Update Pivot Table Source VBA

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.

Upvotes: 1

Views: 3217

Answers (1)

Tim Swingle
Tim Swingle

Reputation: 45

        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.

Upvotes: 1

Related Questions