Excel透视表VBA运行时错误1004及字段重复问题求助

问题根源 #

• 首次运行后Column1显示为Column1_2：重复添加相同数据字段，或旧透视表缓存残留导致Excel自动重命名字段
• 再次运行触发1004错误：固定名称PivotTable4已存在，或目标工作表中的旧透视表未被彻底清除（Cells.Clear只清内容，不删透视表对象）

修复步骤 #

1. 彻底清除旧透视表 #

在清除单元格内容前，先删除目标工作表中所有透视表对象：

' 插入到ws.Cells.Clear之前
Dim existingPt As PivotTable
For Each existingPt In ws.PivotTables
    existingPt.TableRange2.Clear
    existingPt.Delete
Next existingPt
ws.Cells.Clear

2. 避免透视表名称冲突 #

不要用固定名称，改用时间戳生成唯一名称，或者让Excel自动分配：

' 替换原创建透视表的代码
Set pt = ws.PivotTableWizard(SourceType:=xlDatabase, _
                             SourceData:=ptRange, _
                             TableDestination:=ws.Range("A1"), _
                             TableName:="PivotTable_" & Format(Now(), "YYYYMMDDHHMMSS"))

3. 防止数据字段重复添加 #

添加字段前先检查是否已存在，避免重复导致后缀_2：

' 替换原添加Column1字段的代码
Set ptField = .PivotFields("Column1")
Dim df As PivotField
Dim fieldExists As Boolean
fieldExists = False

For Each df In .DataFields
    If df.SourceName = "Column1" Then
        fieldExists = True
        Exit For
    End If
Next df

If Not fieldExists Then
    ptField.Orientation = xlDataField
    ptField.Function = xlSum
    ptField.Name = "Sum of Column1" ' 手动指定名称，避免自动加后缀
End If

完整修复代码 #

Sub Pivot()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim ptField As PivotField
    Dim ptRange As Range
    Dim last_row As Long
    Dim sheet_from As Worksheet
    Dim existingPt As PivotTable
    Dim df As PivotField
    Dim fieldExists As Boolean

    ' 设定透视表目标工作表
    Set ws = Sheets("Sheet2")
    ' 彻底清理旧透视表
    For Each existingPt In ws.PivotTables
        existingPt.TableRange2.Clear
        existingPt.Delete
    Next existingPt
    ws.Cells.Clear

    ' 定义数据源范围
    Set sheet_from = ActiveSheet
    last_row = sheet_from.Range("A65336").End(xlUp).Row
    Set ptRange = sheet_from.Range("A2:BJ" & last_row)

    ' 创建带唯一名称的透视表
    Set pt = ws.PivotTableWizard(SourceType:=xlDatabase, _
                                 SourceData:=ptRange, _
                                 TableDestination:=ws.Range("A1"), _
                                 TableName:="PivotTable_" & Format(Now(), "YYYYMMDDHHMMSS"))

    ' 配置透视表字段
    With pt
        ' 添加行字段Narrative1
        Set ptField = .PivotFields("Narrative1")
        ptField.Orientation = xlRowField

        ' 添加数据字段Column1（避免重复）
        Set ptField = .PivotFields("Column1")
        fieldExists = False
        For Each df In .DataFields
            If df.SourceName = "Column1" Then
                fieldExists = True
                Exit For
            End If
        Next df
        If Not fieldExists Then
            ptField.Orientation = xlDataField
            ptField.Function = xlSum
            ptField.Name = "Sum of Column1"
        End If

        ' 设置数据字段为列
        With .DataPivotField
            .Orientation = xlColumnField
            .Position = 1
        End With
    End With

    sheet_from.Activate
End Sub

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