运行时错误'1004'：PivotTable字段名无效
运行时错误'1004'：PivotTable字段名无效
提问于 2018-06-07 02:07:14
我在同一个电子表格中创建了一个第8个数据透视表，最后一个数据透视表出现以下错误：
“运行时错误'1004'：PivotTable字段名称无效。若要创建PivotTable报告，必须使用组织为标签列列表的数据。如果要更改PivotTable字段的名称，则必须为该字段键入新名称。"
但是，我已经多次尝试更改透视表的名称，但问题都没有解决。
我的代码如下：
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")回答 1
Stack Overflow用户
回答已采纳
发布于 2018-06-11 07:47:57
这个答案是由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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50726893
复制相关文章
相似问题