Macro to update pivot table data range

[a.barker728]

Hi all,
I am trying to create Macro in excel 2010 which will change the data range for a pivot table if have added extra datainto the source data worksheet. This is the macro belowbut it returns a runtime error 5 "Invalid procedure Call) error:

Sub ChangePivotTableDataRange()

ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Worksheets("Sales").Range("A5").CurrentRegion.Address _
, Version:=xlPivotTableVersion14)

End Sub

Any help on this would be greatly appreciated.

Taffy

 

[benmcclave]

Taffy,

I think the issue is in the "SourceData" argument. If you change it to:

SourceData:="Sales!" & Worksheets("Sales").Range("A5").CurrentRegion.Address

it should work.

Hope this helps,
Ben

 

[a.barker728]

Taffy,



I think the issue is in the "SourceData" argument. If you change it to:



SourceData:="Sales!" & Worksheets("Sales").Range("A5").CurrentRegion.Address



it should work.



Hope this helps,

Ben

Hi Ben,
Thanks for your help - it worked a treat! I'm always amazed at how quick you guys post your responese.

Thanks again,

Taffy

 

[benmcclave]

Glad it worked, and happy to help.