Macro to update pivot table data range
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").ChangePivot Cache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Worksheets("Sales").Range("A5").Curren tRegion.Address _ , Version:=xlPivotTableVersion14) End Sub Any help on this would be greatly appreciated. Taffy |
Macro to update pivot table data range
Taffy,
I think the issue is in the "SourceData" argument. If you change it to: SourceData:="Sales!" & Worksheets("Sales").Range("A5").CurrentRegion.Addr ess it should work. Hope this helps, Ben |
Macro to update pivot table data range
On Sunday, 9 September 2012 19:53:38 UTC+1, (unknown) wrote:
Taffy, I think the issue is in the "SourceData" argument. If you change it to: SourceData:="Sales!" & Worksheets("Sales").Range("A5").CurrentRegion.Addr ess 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 |
Macro to update pivot table data range
Glad it worked, and happy to help.
|
All times are GMT +1. The time now is 10:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com