From 2007 to 2003 : Changing data source in Pivot Tables in macro

[ljubinville]

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Situation : I have an exel worksheet wich contains 2 sheets. The first is the data and second contains 4 pivot tables. It also contains a macro that will update the Data source of all 4 pivot tables since the data changes over time. The macro was designed in Excel 2007 and works perfectly, but would not work in Excel 2003 (the data source starts at A2 and contains only 1 line).<o></o>
<o> </o>
Here is the macro code :<o></o>
<o> </o>
ActiveSheet.PivotTables("Pivot table 1").ChangePivotCache _<o></o>
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _<o></o>
Path & "[" & File & "]" & DataSheet & "!R1C1:R" & EndLine & "C" & EndColomn _<o></o>
, Version:=xlPivotTableVersion10)<o></o>
<o> </o>
How would you design this to work in Office 2003 ?<o></o>

 

[GlennUK]

Can I ask why you are using a macro to change the source over time? If you create a defined name that calculates the data range, and reference that as the PivotTable source, everything would work automatically without the need to run a macro to redefine the source data.