Macro Frustration

[Rochelle]

Firstly - I don't know anything about programming. I am recording Macros in Excel to create a series of pivot tables from a range of data. I've done this before and it has worked perfectly well.

I'm now trying to record a new macro to create new pivot tables in the same workbook, I think I'm doing everything the same way, but I keep getting a message that reads - "Add field method of pivot table class failed" or words to that effect.

What's going on???

 

[IANW]

if you are recording it, the macro will probably be trying to give the new pivot table the same name as the old one

TableName:="PivotTable1"

if you change the name of the pivot table to something different the macro should work fine

 

[Rochelle]

Thanks for that. I checked the pivot table names and that doesn't appear to be the problem.

I have also tried deleting all the previous macros and starting again - no go... I get either "Unable to get the PivotFields property of the PivotTable class", or "Add fields method of PivotTable class failed."

I suppose I'll have to and pay for some VB training. It just annoys me that it all worked the first time I did it, and I can't find anything different to explain why it won't work now.

This is from the Macro that works:

ActiveWorkbook.PivotCaches.Add (SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R65536C5").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable10", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable10").AddFields RowFields:=Array("cfrnum", _
"dcalldate", "ccalltime"), ColumnFields:="ccallstat", PageFields:= _
"cfrexchng"
ActiveSheet.PivotTables("PivotTable10").PivotFields("ccallstat").Orientation = _
xlDataField

This is from the one that doesn't work:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!C1:C4").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array("cmnth", _
"cdwk", "chour"), ColumnFields:="ccallstat"
ActiveSheet.PivotTables("PivotTable2").PivotFields("ccallstat").Orientation = _
xlDataField

????
This message was edited by Rochelle on 2002-11-07 20:03