VBA脚本本地运行正常，同事使用时Pivot Cache报错求助

你的脚本在本地正常但同事运行报错，核心原因是未明确限定工作表对象以及兼容性写法问题，以下是具体修正方案：

错误根源 #

• 依赖Activate和ActiveSheet操作工作表，同事环境中当前激活的工作表可能不是Sheet1，导致Range引用错误
• 使用数字1代替xlDatabase常量，不同Excel版本可能存在常量值差异
• LastRow获取时未指定工作表，可能取到其他表的无效行数，导致数据源范围错误

修正后的完整代码 #

Sub pivot_qty()
    Dim PCache As PivotCache, LastRow As Long, pt As PivotTable
    Dim sourceSheet As Worksheet, pivotSheet As Worksheet
    
    ' 删除已存在的"Pivot qty"工作表
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Sheets("Pivot qty").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    
    ' 绑定数据源工作表
    Set sourceSheet = ThisWorkbook.Worksheets("Sheet1")
    
    ' 获取数据源表的有效最后一行
    LastRow = sourceSheet.Cells.Find("*", sourceSheet.Range("A1"), , , xlByRows, xlPrevious).Row
    
    ' 创建Pivot Cache，明确指定数据源和标准常量
    Set PCache = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=sourceSheet.Range("A1:AV" & LastRow))
    
    ' 添加并命名透视表工作表
    Set pivotSheet = ThisWorkbook.Worksheets.Add
    pivotSheet.Name = "Pivot qty"
    
    ' 创建数据透视表
    Set pt = pivotSheet.PivotTables.Add( _
        PivotCache:=PCache, _
        TableDestination:=pivotSheet.Range("A1"), _
        TableName:="PivotTable1")
    
    ' 设置透视表行字段
    With pt.PivotFields("Plant")
        .Orientation = xlRowField
        .Position = 1
    End With
    With pt.PivotFields("Service Desc")
        .Orientation = xlRowField
        .Position = 2
    End With
    With pt.PivotFields("Price Per Unit")
        .Orientation = xlRowField
        .Position = 3
    End With
    
    ' 关闭字段列表
    ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

关键修改说明 #

• 用ThisWorkbook固定操作当前脚本所在工作簿，避免同事打开多工作簿时混淆
• 声明sourceSheet和pivotSheet对象，所有单元格引用都绑定到具体工作表，彻底取消对Activate的依赖
• 替换数字1为xlDatabase常量，提升代码可读性和跨版本兼容性
• LastRow获取限定在数据源表内，确保行数准确
• 所有工作表操作通过对象引用完成，避免激活状态变化引发的错误

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