基于动态范围创建数据透视表的VBA宏1004错误排查求助

我帮你排查了代码里的几个关键问题，这些正是触发1004错误的根源，下面逐一说明并给出修复后的完整代码：

主要错误点分析 #

• 工作表名称大小写不匹配：你创建新工作表时命名为"Pivot Table"（注意Table首字母大写），但后续引用时写的是Worksheets("Pivot table")（小写t），Excel对工作表名称大小写敏感，这会导致找不到目标工作表直接报错。
• 未指定startCell所属工作表：Set startCell = Range("A18")默认指向当前活动工作表，而非Sheet1的A18，可能导致后续计算最后一行/列时出错。
• 数据源范围选择错误：你用pdsheet.Cells(1, 1).Resize(plr, plc)把数据源设为从A1开始，但实际数据从A18起步，这会包含大量空行空列，导致透视表缓存识别数据源异常。

修复后的完整VBA代码 #

Sub CreatePayrollPivot()
    Dim pvtable As PivotTable
    Dim pvcache As PivotCache
    Dim ptrange As Range
    Dim pvsheet As Worksheet
    Dim pdsheet As Worksheet
    Dim plr As Long
    Dim plc As Long
    Dim startCell As Range

    On Error Resume Next
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Worksheets("Pivot Table").Delete ' 统一名称大小写，避免找不到工作表
    On Error GoTo 0
    
    ' 添加并命名新工作表，保持名称一致
    Set pvsheet = Worksheets.Add(After:=ActiveSheet)
    pvsheet.Name = "Pivot Table"
    
    Set pdsheet = Worksheets("Sheet1")
    ' 明确指定startCell属于Sheet1
    Set startCell = pdsheet.Range("A18")

    ' 计算从A18开始的最后一行和最后一列
    plr = pdsheet.Cells(pdsheet.Rows.Count, startCell.Column).End(xlUp).Row
    plc = pdsheet.Cells(startCell.Row, pdsheet.Columns.Count).End(xlToLeft).Column

    ' 正确设置数据源范围：从A18开始到最后一行最后一列
    Set ptrange = pdsheet.Range(startCell, pdsheet.Cells(plr, plc))

    ' 创建透视表缓存，指定正确的数据源
    Set pvcache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=ptrange)

    ' 创建透视表，确保目标工作表引用正确
    Set pvtable = pvcache.CreatePivotTable( _
        TableDestination:=pvsheet.Cells(1, 1), _
        TableName:="Payroll")

    ' 添加行字段：Worker
    With pvtable.PivotFields("Worker")
        .Orientation = xlRowField
        .Position = 1
    End With

    ' 添加行字段：Actual Hours
    With pvtable.PivotFields("Actual Hours")
        .Orientation = xlRowField
        .Position = 2
    End With

    ' 设置表格为 tabular 格式
    pvtable.RowAxisLayout xlTabularRow

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

关键修正说明 #

1. 统一工作表名称大小写：把所有引用"Pivot table"的地方改成"Pivot Table"，确保创建和引用的名称完全一致。
2. 指定startCell的工作表：Set startCell = pdsheet.Range("A18")明确绑定到Sheet1，避免活动表切换导致的错误。
3. 修正数据源范围：用pdsheet.Range(startCell, pdsheet.Cells(plr, plc))直接从A18开始到数据的最后一行最后一列，避免包含无关的空区域。
4. 优化透视表对象引用：创建透视表后直接用pvtable对象操作字段，避免重复通过工作表查找，代码更高效清晰。

这样修改后，应该就能顺利生成数据透视表，不会再触发1004错误了。

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