vba - 运行时错误“1004”:数据透视表字段名称无效
问题描述
我在同一个电子表格中创建了第 8 个数据透视表,并且在最后一个数据透视表中出现以下错误:
“运行时错误'1004':数据透视表字段名称无效。要创建数据透视表报表,您必须使用组织为标记列列表的数据。如果要更改数据透视表字段的名称,则必须键入字段的新名称。”
但是,我多次尝试更改数据透视表的名称,但问题仍未解决。
我的代码如下:
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 提供:
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
推荐阅读
- matlab - 我可以使用不同的循环来执行此操作,以及如何使其成为函数?
- javascript - 为网站创建 google 登录,但收到“错误:invalid_client 未找到 OAuth 客户端”。错误
- python - 以特定时间间隔运行脚本
- android - Android:来自 Firebase 存储的音频流使用了太多数据
- google-slides-api - Google slides API ReplaceAllShapesWithImageRequest:如何确保图像保持相同的形状?
- .net - 使用身份在 asp.net 核心中调试时为当前用户添加或删除角色?
- android - 改造不会序列化我的响应类中的第二个对象
- python - Sklearn 目标数据 - 只有整数标量可以转换为标量索引
- django - 如何将 slug 传递给 Django 中的 URL?
- javascript - Discord 在消息字符串中添加逗号