Run-time error '1004': The Pivot Table field name is not valid

S

shawnrpg

New Member
Joined
Oct 23, 2009
Messages
31
Hi all,

I have a rather lengthy macro that concules by refreshing two different pivot tables. This macro has been in use for several months now with no issues but yesterday the user called me and stated that she was getting an error.
The error is "Run-time error '1004': The Pivot Table field name is not valid. To create a Pivot Table report, you must use data that is organized as a list with labeled columns. If you are changing the name of the Pivot Table field, you must type a new name for the field."

The potion of the code where the error occurs is on the "PivotCache.Refresh" line of the following:
Sheets("Quote_RFQ_Received").Select
ActiveSheet.PivotTables("PivotTable3").PivotSelect "", xlDataAndLabel, True
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
Range("B4").Select

It appears that the code doesn't like the name "PivotTable3" -- although it didn't have a problem with it before yesterday.

I found a similar thread to this on this message board but the responses were to ensure that the PT name in the code matched what is actually on the PT in the spreadsheet. I followed the different examples to determine the actual table name and I can confirm that the name in the code matches PT field name.
I also confirmed that each of the three PT columns had headers.

I tried the macro on a couple other machines (besides the daily user) and the same error occurs. Now here's the really odd thing; when I run the macro on my machine, it works fine.

Any ideas on 1) why it has worked for the past couple months and all of a sudden an error occurs, 2) why the macro works fine on my machine but not others, and most importantly 3) what is the fix.

Thanks for your help,

Shawn
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
I forgot to mention that I am using Excel 2007.

Andrew,
how do I find the SourceData property of the PivotCache? Or, I guess, where do I find the PivotCache?

There are 3 columns in the PT; column 1 is Item #, column 2 is Quote Date, and column 3 is Total.
The data for the pivot table is a very large spreadsheet flatfile that has 15 columns with varying types of information. When the macro runs, it opens that spreadsheet and copies all the columns to a tab (Quote Log Data) in the file that the macro is run in.
When I click on Change Source Data, it is pointing to the correct tab/columns that contain the data used in the PT.

Does this help?

Shawn
 
Upvote 0
To find what the SourceData property contains:

MsgBox ActiveSheet.PivotTables("PivotTable3").PivotCache.SourceData

Is everybody using Excel 2007?
 
Upvote 0
First, everyone here is using Excel 2007.

I found that the SourceData for the PivotCache is 'Quote Log Data'!C1:C19.
This is obviously not all the data from the source data and not what is in the pivot table. I guess I need to adjust the PivotCache source data; how do I do that?

Shawn
 
Upvote 0
Oh, I thought it meant cells C1 through cell C19. I have other code where I grab all the data in a range of columns by using 'Columns("A:J").Copy'; when I grab a subset of data within a column, I use 'Range("A5:A20").Select'.

The column headers grabbed by the source data are Item #, Customer, RFQ Type, Plant, ISR, Acct Mgr, Part Number, End Pgm, RFQ Rec'd, Quote Admin Rec'd, Est/DRC, Sent for Pricing, Marketing Manager, Pricing Received, and Quote Date.
However, there are more columns in the actual source data; they are Quote Number, Quoted Lead Time, and Comments. There are also 5 hidden columns between Quote Date, Quote Number, and Quote Turnaround.

It looks like there are 19 columns visible on the Quote Log Data where the source data is coming from. I wonder is the user of this file added a column and that hosed the code.

I also wonder if I change the source data to include all 19 columns if the problem goes away. I will try that next; although that doesn't explain why I'm able to the macro without getting an error.

Shawn
 
Upvote 0
Can you refresh the table manually?
If you select a cell in the table, then press Alt+D then P, then step back through the wizard, what range does it show as the source data?
 
Upvote 0
I haven't tried the last two suggestions yet. What I did do was expand the lookup range from the source data from 'Quote Log Data'!$A:$S to 'Quote Log Data'!$A:$W to match the number of columns (19) in the pivot cache.
This seems to have worked because when I had the user try the macro this morning, it ran to completion.

The one thing I did find out is the end user add several columns to the spreadsheet, where the source data comes from, the other day. I wonder if the way she added the columns affected the pivot table.

When I get some free time, I will also try out the other suggestions that Andrew and rorya posted.

Thanks for all of your help!!!

Shawn
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Macro to drilldown on a specific pivot table row item
Replies
1
Views
260
ddander54
D
W
  • Solved
Multiple pivot tables on the same sheet
Replies
2
Views
265
Woftae
W
P
  • Question
Run-time error '424': on Pivot Refresh
Replies
0
Views
178
programsam
P
C
  • Question
Pivot Table from Power Query - reorder Columns (Excel 2021)
Replies
4
Views
276
craigcurtin
C
ajjava
  • Question
Procedure to find MAX VALUE in a SPECIFIC pivot table value field, in the case of MULTIPLE pivot table value fields
Replies
0
Views
814
ajjava
ajjava

Forum statistics

Threads
1,214,561
Messages
6,120,234
Members
448,951
Latest member
jennlynn

Share this page

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
Back
Top