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

Question

I am creating an 8th Pivot Table in the same spreadsheet and I am getting the following error for the LAST PIVOT TABLE:

"Run-time error '1004': The PivotTable field name is not valid. To create a PivotTable report you must use data that is organized as a list of labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field. "

However, I have tried to change the name of Pivot Table several times and the issue has not been resolved.

My code is as follows:

Sub Macro1()
'Pivot Table 8
'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim LastRow As String
Dim LastCol As String
Dim SData As String
Dim PCache As PivotCache
Dim PTable As PivotTable

Set PSheet = Worksheets("US MASTER")
Set DSheet = Worksheets("US Master Macro")

LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
SData = "'US Master Macro'!R1C1:R" & LastRow & "C" & LastCol

Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=SData)

Set PTable = PCache.CreatePivotTable(TableDestination:=Worksheets("US MASTER").Range("Y4"), TableName:="InfoView Cases")

With ActiveSheet.PivotTables("InfoView Cases")
 .SmallGrid = False
 'Add Days to Row Field
 With .PivotFields("Age of Case")
 .Orientation = xlRowField
 .Position = 1
 End With
 'Add PR ID to Values Field
 With .PivotFields("PR ID")
 .Orientation = xlDataField
 .Function = xlCount
 .Position = 1
 End With
 'Add Filter
 With .PivotFields("SAP Notification")
 .Orientation = xlPageField
 .Position = 1
 End With
 'Add Filter
 With .PivotFields("Case Status")
 .Orientation = xlPageField
 .Position = 2
 End With
End With

'Deselect Filter

    l = ActiveSheet.PivotTables("InfoView Cases").PivotFields("SAP Notification"). _
        PivotItems.Count - 1

    For k = 1 To l
        With ActiveSheet.PivotTables("InfoView Cases").PivotFields("SAP Notification")
            .PivotItems(k).Visible = False
        End With
    Next k

        With ActiveSheet.PivotTables("InfoView Cases").PivotFields("SAP Notification")
            .PivotItems("(blank)").Visible = True
        End With

'Deselect Filter

    n = ActiveSheet.PivotTables("InfoView Cases").PivotFields("Case Status"). _
        PivotItems.Count - 1

    For m = 1 To n
        With ActiveSheet.PivotTables("InfoView Cases").PivotFields("Case Status")
            .PivotItems(m).Visible = False
        End With
    Next m

        With ActiveSheet.PivotTables("InfoView Cases").PivotFields("Case Status")
            .PivotItems("(blank)").Visible = True
        End With


'Add InfoView Cases
PSheet.Range("Y3").Value = "InfoView Cases"
PSheet.Range("Y4").Value = "Days"
'Merge
PSheet.Range("Y3:Z3").Merge

'Sort Pivot Table
    Range("Y5:Y100").Select
    ActiveSheet.PivotTables("InfoView Cases").PivotFields("Age of Case").AutoSort _
        xlAscending, "Age of Case"


End Sub

My code breaks in the line

Set PTable = PCache.CreatePivotTable(TableDestination:=Worksheets("US MASTER").Range("Y4"), TableName:="InfoView Cases")

@QHarr will do! Your answer did fix the issue but the code was not provided... I will vote for your answer but also will provide the code as well. Thank you for reminding me! — Myriam Diaz Martinez
What does the top couple rows of your data (that creates your pivot cache) look like? The error says "PivotTable field name is not valid", which implies to me that one of the items in the first (header) row is probably a number or some other invalid value to be used as a field name. — PeterT
@PeterT Its a table with the first row as the header and the rest of the rows as the actual values for the table. None of the headers is a number. — Myriam Diaz Martinez
Make the SData range a table. The data source is rows & columns anyway; an actual named table ("format as table" from the home ribbon) is always a valid pivot table data source - watch how Excel fixes whatever heading is wrong as you convert the range to a table. — Mathieu Guindon
@MathieuGuindon The source data is a formatted table. The code works perfectly for the first 7 Pivot Tables and ignores the 8th one-The one that I put the code for above. — Myriam Diaz Martinez

Myriam Diaz Martinez Myriam Diaz Martinez · Accepted Answer · 2018-06-10T23:47:57

This answer was provided by Mathieu Guindon:

Dim srcRange As Range
sTable = "DataTable"
   Set ListObj = ActiveSheet.ListObjects.Add(xlSrcRange, [A1].CurrentRegion, , xlYes)
   ListObj.Name = sTable 'The name for the table
   ListObj.TableStyle = "TableStyleDark8"
Dim srcRange_s As String
 srcRange_s = ActiveSheet.ListObjects("DataTable")

'Pivot Table 8
'Pivot Table 8
'Pivot Table 8

'Define Pivot Cache and Insert Pivot Table
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=srcRange_s)

Set PTable = PCache.CreatePivotTable(Range("Y4"), TableName:="InfoView Cases")
'Add Fields
With ActiveSheet.PivotTables("InfoView Cases")
 .SmallGrid = False
 'Add Days to Row Field
 With .PivotFields("Age of Case")
 .Orientation = xlRowField
 .Position = 1
 End With
 'Add PR ID to Values Field
 With .PivotFields("PR ID")
 .Orientation = xlDataField
 .Function = xlCount
 .Position = 1
 End With
 'Add Filter
 With .PivotFields("SAP Notification")
 .Orientation = xlPageField
 .Position = 1
 End With

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

1 Answers