Automating a Pivot Table in Excel2002

[VictoriaLJones]

I am trying to generate a pivot table in order to run some comparisons between 2 sets of reports. However - I have recorded generating a pivot table via the recorder, but when I have come to re-run the macro it falls over with 40036 - Application-defined or Object-defined error, with no debug option. I have managed to ascertain that it is the AddFields code that is falling over. Having never used 2002 before (97 the norm) can anyone tell me if I am doing something wrong...

I have copied the relevant code below.

Any suggestions would be gratefully received! (Oh and apologies for the clumsiness of the code!!)

Many thanks
Victoria


Workbooks.Open Filename:= _
"C:\Documents and Settings\carla\My Documents\Credit Lyonnais_old.xls"
Workbooks.Open Filename:= _
"C:\Documents and Settings\carla\My Documents\Credit Lyonnais_new.xls"
Windows("Credit Lyonnais_New.xls&quot.Activate
'Range("H1&quot.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Sub-Acct 1'!R4C1:R87C6&quot.CreatePivotTable TableDestination:= _
"'[Credit Lyonnais_new.xls]Sub-Acct 1'!R3C8", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable1&quot.AddFields RowFields:=Array( _
"Fund Family", "Ticker", "Shares", "NAV", "Market Value&quot
ActiveSheet.PivotTables("PivotTable1&quot.PivotFields("Fund Name&quot.Orientation = _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable&quot.Visible = False
Range("I8&quot.Select
Application.PivotTableSelection = True
ActiveSheet.PivotTables("PivotTable1&quot.PivotSelect "Ticker[All;Total]", _
xlDataAndLabel, True

 

[xlbo]

Can't see anything particularly wrong there. Could it be that some changes have been made between recording the macro and trying to play it. Most common cause would be a slight renaming of one of the fields....

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes