BobTurnbull
07-27-2012, 01:40 AM
Hello,
I need to set up a macro that will update the source data of a pivot table to include all data on a worksheet as the data worksheet will be added to periodically but i need the structure of the pivot table to remain the same.
Recording a macro gives the following code:
Worksheets("PIVOT").Select
ActiveSheet.PivotTables("PivotTable12").ChangePivotCache
_ActiveWorkbook.PivotCaches.Create(xlDatabase, _
"C:\Bob\[excelworkbook.xlsm]DATA!R1C1:R65604C126", xlPivotTableVersion14)
This works fine but I need the macro to include all data from the "DATA"
sheet not a defined number of rows and colums. The following code works for
another workbook I have but not for this, it returns a Runtime-13 error and I
can't see why:
Worksheets("CUSTPIVOT").Select
ActiveSheet.PivotTables("PivotTable12").ChangePivotCache
ActiveWorkbook.PivotCaches.Create _
(xlDatabase, Worksheets("DATA").UsedRange, xlPivotTableVersion14)
All I've done is change the SourceData and despite the fact that the PivotCaches.Create method takes in a Range Object as the Source data the first code works but the second doesn't, even though the second code works in another workbook.
Any ideas? I'm completely stumped.
Thanks
Bob
I need to set up a macro that will update the source data of a pivot table to include all data on a worksheet as the data worksheet will be added to periodically but i need the structure of the pivot table to remain the same.
Recording a macro gives the following code:
Worksheets("PIVOT").Select
ActiveSheet.PivotTables("PivotTable12").ChangePivotCache
_ActiveWorkbook.PivotCaches.Create(xlDatabase, _
"C:\Bob\[excelworkbook.xlsm]DATA!R1C1:R65604C126", xlPivotTableVersion14)
This works fine but I need the macro to include all data from the "DATA"
sheet not a defined number of rows and colums. The following code works for
another workbook I have but not for this, it returns a Runtime-13 error and I
can't see why:
Worksheets("CUSTPIVOT").Select
ActiveSheet.PivotTables("PivotTable12").ChangePivotCache
ActiveWorkbook.PivotCaches.Create _
(xlDatabase, Worksheets("DATA").UsedRange, xlPivotTableVersion14)
All I've done is change the SourceData and despite the fact that the PivotCaches.Create method takes in a Range Object as the Source data the first code works but the second doesn't, even though the second code works in another workbook.
Any ideas? I'm completely stumped.
Thanks
Bob