Solved: ChangePivotCache - PivotCaches.Create Problems

[BobTurnbull]

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:

[vba]Worksheets("PIVOT").Select
ActiveSheet.PivotTables("PivotTable12").ChangePivotCache
_ActiveWorkbook.PivotCaches.Create(xlDatabase, _
"C:\Bob\[excelworkbook.xlsm]DATA!R1C1:R65604C126", xlPivotTableVersion14)[/vba]
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:[vba]
Worksheets("CUSTPIVOT").Select
ActiveSheet.PivotTables("PivotTable12").ChangePivotCache
ActiveWorkbook.PivotCaches.Create _
(xlDatabase, Worksheets("DATA").UsedRange, xlPivotTableVersion14)
[/vba]

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

[Simon Lloyd]

Make sure your data area is a dynamic named range http://www.contextures.com/xlNames01.html then in the worksheet_activate for your pivottable enter this[vba]Me.PivotTables("PivotTable1").PivotCache.Refresh[/vba]replacing PivotTable1 for your pivot table name, now whenever you view that worksheet it will refresh to include ALL the data

This may also interest you when working with pivot tables http://www.thecodecage.com/forumz/mi...bles-once.html

[BobTurnbull]

Thanks very much for your help, it appears to be working now using the define name trick.

Bob