Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to update pivot table data range
Glad it worked, and happy to help.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
update pivot table data range | Charts and Charting in Excel | |||
Auto Update Range of a Pivot Table | Excel Programming | |||
How do I set the pivot table to auto-update the data range? | Excel Discussion (Misc queries) | |||
Macro to update pivot table data range | Excel Programming | |||
Update Data Field in Pivot Table with Dynamic Excel Range | Excel Programming |