So frustrated with this: "Run-time error '1004': The PivotTable field name is not valid"

Hi everyone, I'm hoping someone could shed some light on this issue; I have done many searches and spent hours trying to resolve this problem.

I'm getting the following error which by the looks of it many others are running into.

Run-time error '1004':

The PivotTable field name is not valid; To create a PivotTable report, you must use the data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.

I have built my macro by recording, and then going in to tweak the code. I'm not a programmer, starting with pure VBA is not really an option. Basically I'm putting the cursor at R1C1 on the table, then inserting a Pivot Table.

This works just fine when I do it within the spreadsheet. However, when the macro runs, it throws the error and then points to this part of the macro code (with **s)


Range("A1").Select
Application.CutCopyMode = False
Sheets.Add
* ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
* "Open Quote Raw!R1C1:R10000", Version:=xlPivotTableVersion15). _
* CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable3" _
* , DefaultVersion:=xlPivotTableVersion15
Sheets("Sheet1").Select


I don't get why this isn't working int the macro when it works just fine manually. The table is fine, there are no blank headings or even blank cells. When I create it manually, it creates it with the same name, PivotTable3. I don't know what else to check...

What is this refering to "The PivotTable field name is not valid;" what name?? This is driving me insane and it's consumed hours of my day yesterday, as well as a lot of pulled hairs...

Any help would be appreciated.
TY

Hello my friend

Put you Code into CODE tags

This way:

Please Login or Register  to view this content.

By the way, could you please post your workbook?

I don't know much about this kind of thing however I have had some problems between Manually doing things and Macro's usually the errors relate to the cell references, to ascertain if this might the case for you, do you have any merged cells that you are pulling data from? If so then you might need to reference the starting cell of the merged cell.

For instance if Cell B2,B3 & B4 are merged then set the reference to cell B2

Originally Posted by Gatti

Hello my friend

Put you Code into CODE tags

This way:

Please Login or Register  to view this content.

By the way, could you please post your workbook?

Thanks for the tip!

I'm not sure what you're asking - are you asking for the table on which I'm trying to create a pivot? I can't post that as it contains proprietary and confidential information...

Ok, I solved the problem and this took me literally 4 hours of work [Excel 2013 btw, I forgot to post this info]

-F8 to step through does not work, I hit F8, the next time I hit F8 it just blasts through the rest of the code, not sure what's wrong.
-I just put stops at lines (click on edge and put in a circle) to see exactly where the issue was and it pointed to ALL of the code below so I didn't notice that I was missing the Column there. Ugh, so much time wasted, the debugger totally sucks that I couldn't tell me this was the problem.

Please Login or Register  to view this content.

Anyway, after putting in C22 everything else worked.

Cheers all!