运行时错误"1004":数据透视表字段名称无效 [英] Run-time error '1004': The PivotTable field name is not valid

问题描述

我在同一电子表格中创建了第8个数据透视表，但最后一个PIVOT表却出现以下错误:

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

运行时错误'1004':数据透视表字段名称无效.若要创建数据透视表报表，您必须使用组织为带标签的列的列表的数据.如果要更改数据透视表字段的名称，则必须为该字段键入一个新名称."

但是，我尝试多次更改数据透视表的名称，但该问题尚未解决.

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

我的代码如下:

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

我的代码在行中中断

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

推荐答案

此答案由Mathieu Guindon提供:

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

这篇关于运行时错误"1004":数据透视表字段名称无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！