Excel VBA录制宏创建数据透视表报运行时错误1004排查

问题现象 #

通过录制宏方式创建数据透视表时触发报错：运行时错误'1004' 应用程序定义或对象定义错误。
经定位，报错触发于ActiveWorkbook.PivotCaches.Create()方法执行环节，初步判断为SourceData参数配置异常导致。
触发报错的代码片段：

Sub Macro10()
'
' Macro10 Macro
'

'
    Columns("A:I").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "FoodSales!R1C1:R1048576C9", Version:=7).CreatePivotTable TableDestination _
        :="Sheet16!R3C1", TableName:="PivotTable8", DefaultVersion:=7
    Sheets("Sheet16").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable8")
        .ColumnGrand = True

预期实现的透视表结构：

• 行字段：City
• 列字段：Product
• 值字段：Total Price 求和汇总

原始完整宏代码 #

Sub Macro10()
'
' Macro10 Macro
'

'
    Columns("A:I").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "FoodSales!R1C1:R1048576C9", Version:=7).CreatePivotTable TableDestination _
        :="Sheet16!R3C1", TableName:="PivotTable8", DefaultVersion:=7
    Sheets("Sheet16").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable8")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable8").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable8").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("PivotTable8").PivotFields("City")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable8").PivotFields("Product")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable8").AddDataField ActiveSheet.PivotTables( _
        "PivotTable8").PivotFields("TotalPrice"), "Sum of TotalPrice", xlSum
End Sub

问题根因 #

报错由两处宏录制生成代码的固有缺陷导致：

• 工作表名硬编码不匹配：代码先执行Sheets.Add新建工作表，Excel对新建工作表的命名遵循序号递增规则，只有当前工作簿已存在Sheet1~Sheet15时，新表才会被命名为Sheet16。如果本地已存在Sheet16，或新表默认名称不符合预期，后续TableDestination:="Sheet16!R3C1"会因找不到目标位置触发错误。
• 数据源引用不规范：SourceData参数直接传入整列范围的R1C1格式文本，跨表引用时未做规范限定，且整列104万行包含大量空单元格，会导致透视表缓存创建失败。

修复方案 #

废弃宏录制生成的硬编码逻辑，改用对象变量绑定工作表、动态获取有效数据范围，修复后可直接运行的代码如下：

Sub Macro10_Fixed()
    Dim wsSource As Worksheet, wsPivot As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable
    Dim lastRow As Long
    
    ' 绑定数据源工作表，动态定位有效数据最后一行，避免引用整列空值
    Set wsSource = ThisWorkbook.Worksheets("FoodSales")
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    
    ' 新建透视表存放工作表，直接绑定对象，无需硬编码表名
    Set wsPivot = ThisWorkbook.Worksheets.Add
    
    ' 创建透视表缓存，传入Range对象作为数据源，不使用硬编码R1C1文本
    Set pc = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=wsSource.Range("A1:I" & lastRow), _
        Version:=xlPivotTableVersion15
    )
    
    ' 创建透视表，目标位置直接用新工作表的Range对象
    Set pt = pc.CreatePivotTable( _
        TableDestination:=wsPivot.Range("A3"), _
        TableName:="SalesPivot"
    )
    
    ' 配置透视表属性与字段
    With pt
        .ColumnGrand = True
        .HasAutoFormat = True
        .PreserveFormatting = True
        .RowGrand = True
        .RowAxisLayout xlCompactRow
        
        ' 字段配置：注意字段名必须和数据源表头完全一致
        .PivotFields("City").Orientation = xlRowField
        .PivotFields("Product").Orientation = xlColumnField
        .AddDataField .PivotFields("Total Price"), "Sum of Total Price", xlSum
    End With
End Sub

注意事项 #

• 字段名必须和数据源表头完全匹配，如果源数据表头是带空格的Total Price，代码中不能写为无空格的TotalPrice，否则会触发找不到字段的错误。
• 透视表版本常量不要硬写数字7，根据使用的Excel版本选择对应内置常量即可，兼容性更好。

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