Macro Frustration

Rochelle

New Member
Joined
Aug 3, 2002
Messages
8
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???
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

IANW

New Member
Joined
Jun 18, 2002
Messages
5
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

New Member
Joined
Aug 3, 2002
Messages
8
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
 

Forum statistics

Threads
1,143,640
Messages
5,719,987
Members
422,257
Latest member
Calion

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top