使用VBA创建Excel动态数据透视表时遇‘对象变量未设置’错误求助

错误原因 #

出现object variable or w/block variable not set的核心问题：

• 定义了变量pc As PivotCache，但创建缓存时错误使用了未定义的pivotCache变量，导致pc始终未被赋值，后续调用pc.CreatePivotTable时触发错误。
• 数据源硬编码为固定范围"Data!R8C1:R11C18"，没有利用之前获取的动态数据源mysourcedata，无法覆盖可变行数的数据集。
• 未按照需求新增工作表存放透视表，而是直接放在原Sheet1中。

修复后的完整代码 #

Sub create_pivot()
    Dim mysourcedata As Range, mydestination As Range
    Dim lr As Long, lc As Long, wb As Workbook
    Dim pc As PivotCache, pt As PivotTable
    Dim pivotSheet As Worksheet
    
    Set wb = ThisWorkbook
    ' 获取动态数据源范围（表头A8开始，到数据最后一行最后一列）
    With wb.Sheets("Sheet1")
        lr = .Cells(.Rows.Count, "A").End(xlUp).Row
        ' 若要固定为100000行，替换上面的lr为：lr = 100000
        lc = .Cells(8, .Columns.Count).End(xlToLeft).Column ' 从表头行取最后一列，更准确
        Set mysourcedata = .Range(.Cells(8, 1), .Cells(lr, lc))
    End With
    
    ' 新增工作表存放透视表
    On Error Resume Next ' 处理工作表已存在的情况
    Set pivotSheet = wb.Sheets("透视表")
    If Err.Number <> 0 Then
        Set pivotSheet = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
        pivotSheet.Name = "透视表"
    End If
    On Error GoTo 0
    Set mydestination = pivotSheet.Range("A1") ' 透视表放在新表的A1位置
    
    ' 创建透视缓存，使用动态数据源
    Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mysourcedata, Version:=xlPivotTableVersion15)
    
    ' 创建透视表
    Set pt = pc.CreatePivotTable(TableDestination:=mydestination, TableName:="数据透视表", DefaultVersion:=xlPivotTableVersion15)
    
    ' 示例：添加行字段和值字段（可根据需求修改）
    With pt
        .PivotFields("字段1").Orientation = xlRowField ' 替换为你的实际列名
        .AddDataField .PivotFields("数值字段"), "求和项:数值字段", xlSum ' 替换为你的实际数值列名
    End With
End Sub

关键修复点说明 #

• 变量名匹配：将Set pivotCache = ...改为Set pc = ...，确保定义的pc变量被正确赋值。
• 动态数据源：用mysourcedata替代硬编码范围，支持可变行数；若要固定为100000行，直接将lr = .Cells(.Rows.Count, "A").End(xlUp).Row替换为lr = 100000。
• 新增工作表：添加了创建新工作表的逻辑，避免透视表覆盖原数据，同时处理工作表已存在的异常情况。
• 版本兼容性：将Version:=8改为xlPivotTableVersion15（对应Excel 2013及以后版本），提升兼容性。

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