Writing a Macro to create a pivot table

  • Thread starter Thread starter norcombe
  • Start date Start date
N

norcombe

Hi all,

I would like to create a macro to:

1) Create a Pivot Table
2) Move and rename the resulting worksheet

All goes well when I am recording it, however when the macro tries t
select the data fields and add them to the macro I get the followin
error message:

run-time error '1004'

AddFields method of PivotTable Class Failed.


In the VBA screen the following code is highlighted:

ActiveSheet.PivotTables("PivotTable5").AddFields RowFields:=Array( _
"Level 5 CC", "Level 9 CC", "Level 8 A/C")

Can anybody tell me how I can correct this.

Thank
 
My first guess is that this new pivottable isn't named "pivottable5".

If it's the only one on the worksheet, then maybe this'll work:

ActiveSheet.PivotTables(1).addfields ......
 
Thanks for you help, but I made the change and clicked run and still
received the same error message, any other clues

Thanks
 
My next guess is you changed names of the headers:

"Level 5 CC", "Level 9 CC", "Level 8 A/C"

Are they still there--no extra spaces/no difference in spelling?
 
Thanks again, but I haven't changed anything. To ensure it was not the
wrong numbering of the pivot table I extracted the raw data into a new
workbook and attempted the macro again.

I know you can code Visual Basic directly but I am recording the macro
on the Excel macrorecorder.

Still no joy.

I have searched the internet for the error message and have found one
logged (although it is in german), the website is

http://www.herber.de/forum/archiv/160to164/t162092.htm#162092

Although in German it basically says that this person could do
something in the German version of Excel and not the English. The
recommendation seems to say to record it on the English version with
Excel macrorecorder.

My exact keystrokes were:

Start Recording Macro
Select Pivot Table Wizard from the menu
Selected my area
Selcted my columns and data
Asked for it to be put into a new sheet
Clicked Finish
Moved worksheet
Renamed worksheet
Stopped Recording

It worked whilst I was recording the macro but doesn't when I run the
macro

I'm still stuck so any other ideas are much appreciated.

Thanks
 
I've recorded macros while creating a pivottable and they work when played back.

I don't have a 3rd guess.
 
Back
Top