使用VBA创建数据透视表时出现Error 1004的问题排查与修复请求

错误原因分析 #

• 透视缓存与透视表创建逻辑混淆：原代码在创建PivotCache时直接链式调用CreatePivotTable，导致PCache被错误赋值为透视表对象而非缓存对象，后续用该对象再次创建透视表必然触发错误。
• 重复创建透视表：第一次链式调用已经生成了透视表，后续再次执行PCache.CreatePivotTable属于重复操作，会引发名称冲突或无效引用。
• 透视表名称引用错误：格式化代码中错误引用了不存在的SalesPivotTable，实际应为AccountPivotTable。
• 潜在数据范围问题：需确保数据区域第一行（表头）无空单元格、无重复名称，且数据为连续的列表格式（用户已自查字段名称，此为补充验证点）。

修复后的完整代码 #

Private Sub CreatePivotTable()
    'Declare Variables
    Dim PSheet As Worksheet
    Dim DSheet As Worksheet
    Dim PCache As PivotCache
    Dim PTable As PivotTable
    Dim PRange As Range
    Dim LastRow As Long
    Dim LastCol As Long
    
    'Insert a New Blank Worksheet
    Application.DisplayAlerts = False
    On Error Resume Next ' 避免工作表不存在时删除报错
    Worksheets("PivotTable").Delete
    On Error GoTo 0
    Sheets.Add Before:=ActiveSheet
    ActiveSheet.Name = "PivotTable"
    Application.DisplayAlerts = True
    Set PSheet = Worksheets("PivotTable")
    Set DSheet = Worksheets("Data")
    
    'Define Data Range
    LastRow = DSheet.Cells(DSheet.Rows.Count, 1).End(xlUp).Row
    LastCol = DSheet.Cells(1, DSheet.Columns.Count).End(xlToLeft).Column
    Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
    
    'Define Pivot Cache 正确创建缓存
    Set PCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=PRange)
    
    'Insert Blank Pivot Table 基于缓存创建透视表
    Set PTable = PCache.CreatePivotTable( _
        TableDestination:=PSheet.Cells(1, 1), _
        TableName:="AccountPivotTable")
    
    'Insert Row Fields
    With PTable.PivotFields("Billing Account Number")
        .Orientation = xlRowField
        .Position = 1
    End With
    With PTable.PivotFields("Name Of Service Point")
        .Orientation = xlRowField
        .Position = 2
    End With
   
    'Insert Data Field
    With PTable.PivotFields("Billing Account Number")
        .Orientation = xlDataField
        .Function = xlCount
        .NumberFormat = "#"
        .Name = "Number of Account"
    End With
    
    'Format Pivot Table 修正透视表名称引用
    PTable.ShowTableStyleRowStripes = True
    PTable.TableStyle2 = "PivotStyleLight19"
    
    'Set the layout to "Show in Tabular Form"
    PTable.RowAxisLayout xlTabularRow
    
    'Disable subtotals
    PTable.ColumnGrand = False
    PTable.RowGrand = False
    PTable.DisplayFieldCaptions = False
End Sub

关键修复点说明 #

1. 拆分缓存与透视表创建步骤：先单独创建PivotCache，再基于缓存创建透视表，彻底避免对象类型混淆。
2. 增加删除工作表的错误处理：用On Error Resume Next防止删除不存在的工作表时触发额外错误。
3. 修正透视表名称引用：将格式化代码中的错误名称改为正确的AccountPivotTable，并直接使用PTable对象操作，减少ActiveSheet依赖的潜在风险。
4. 明确工作表行列引用：获取LastRow和LastCol时指定DSheet对象，避免误引用当前活动工作表的行列数。

内容的提问来源于stack exchange，提问作者Phuong Le