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

[shawnrpg]

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

 

[Myproblem]

guys, I have same problem
the VBA code below works before, last week ok, but today not working and the named message was shown
the problem is highlighted
please help me, i spent so much time working on this
original code from book Pivot Table Data Crunching<o></o>

Sub Branch_Amount()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
' Page 206 of Pivot Table Data Crunching<o></o>
Dim WSD As Worksheet<o></o>
Dim PTCache As PivotCache<o></o>
Dim pt As PivotTable<o></o>
Dim PRange As Range<o></o>
Dim FinalRow As Long<o></o>
Set WSD = Worksheets("PivotTable")<o></o>
<o></o>
Application.ScreenUpdating = False<o></o>
<o> </o>
Application.DisplayAlerts = False<o></o>
<o> </o>
<o></o>
<o></o>
' Delete any prior pivot tables<o></o>
For Each pt In WSD.PivotTables<o></o>
pt.TableRange2.Clear<o></o>
Next pt<o></o>
WSD.Range("DA1:KH1").EntireColumn.Clear<o></o>
<o></o>
' Define input area and set up a Pivot Cache<o></o>
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row<o></o>
FinalCol = WSD.Cells(1, Application.Columns.Count). _<o></o>
End(xlToLeft).Column<o></o>
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)<o></o>
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _<o></o>
xlDatabase, SourceData:=PRange.Address)<o></o>
<o></o>
' Create the Pivot Table from the Pivot Cache<o></o>
Set pt = PTCache.CreatePivotTable(TableDestination:=Worksheets("Branch_Amount"). _<o></o>
Cells(2, 2), TableName:="PivotTable1")<o></o>
<o></o>
' Turn off updating while building the table<o></o>
pt.ManualUpdate = False<o></o>
<o></o>
' Set up the row & column fields<o></o>
pt.AddFields RowFields:=Array("AGENCIJA_NAZIV"), _<o></o>
ColumnFields:="Data"<o></o>
<o> </o>
' Set up the data fields<o></o>
With pt.PivotFields("SALDO")<o></o>
.Orientation = xlDataField<o></o>
.Function = xlSum<o></o>
.Position = 1<o></o>
.NumberFormat = "#,###"<o></o>
End With<o></o>
<o></o>
With pt.PivotFields("KATEGORIJA")<o></o>
.PivotItems("A").Visible = True<o></o>
.PivotItems("B").Visible = True<o></o>
.PivotItems("C").Visible = True<o></o>
.PivotItems("D").Visible = True<o></o>
<o></o>
End With<o></o>
<o> </o>
With pt.PivotFields("TIP_KOMITENTA")<o></o>
.PivotItems("B").Visible = True<o></o>
.PivotItems("C").Visible = True<o></o>
.PivotItems("R").Visible = True<o></o>
End With<o></o>
<o></o>
With pt.PivotFields("FILIJALA_NAZIV")<o></o>
.Subtotals(1) = False<o></o>
End With<o></o>
<o></o>
' Calc the pivot table<o></o>
pt.ManualUpdate = False<o></o>
pt.ManualUpdate = False<o></o>
<o></o>
' Format the pivot table<o></o>
pt.ShowTableStyleRowStripes = True<o></o>
pt.TableStyle2 = "PivotStyleMedium10"<o></o>
<o></o>
Worksheets("Branch_Amount").Range("B:H").EntireColumn.AutoFit<o></o>
<o> </o>
End Sub

 

[Andrew Poulsom]

What headings do you have in row 1 of your worksheet?

 

[Myproblem]

i did adequate grouping, like it is the same row heading as last week, it did not changed at all.
i think the main problem is with pivot cache.
i do not know how it can work last week, this week not working

 

[Andrew Poulsom]

Please post a list of your headings. The error indicates that one or more of your headings is invalid. If you select A1 and press End+RightArrow does it take you to the last column in your headings? If you press End+RightArrow again does it take you to IV1? If not you have a blank cell in your header row.

 

[Myproblem]

dear andrew, i know what is needed to make pivot table report - to not have any blank cells in the column header, and i do not have any blanks

i do not know how but now the code is working. i was searching entire web and looking for solution, and i was looking my file from last week and this week.
it seems that the problem was that in the last week file i did not have any links to other columns, which i had this week.
after i made copy / paste as values i got code working great, like last week
i guess i was so lucky to figure out what was happening.
i hope next week i would not have any problems with this code.

thx andrew for helping me, for this week it is working ok