用VBA为多Excel工作表创建数据透视表报错“找不到方法或数据成员”求助

报错核心原因 #

出现“Method or data member not found”的关键问题是错误通过工作表对象（WSheet）调用PivotCaches属性——PivotCaches属于工作簿（Workbook）对象，而非工作表对象，该错误调用会触发成员不存在的报错。

此外代码还存在其他潜在问题：

• 数据源rngSrcData未绑定当前循环的工作表，默认指向活动工作表，循环时会导致数据源错位
• 所有工作表创建同名数据透视表“LATE”，若工作表已存在同名表会触发重复命名错误
• 重复设置PAY CODE为页字段，代码冗余无意义

修正后的完整代码 #

Dim WSheet As Worksheet
Dim rngDestPvt As Range
Dim rngSrcData As Range
Dim pvtCache As PivotCache
Dim pvtTable As PivotTable

For Each WSheet In ActiveWorkbook.Worksheets
    ' 绑定当前工作表的数据源区域
    Set rngSrcData = WSheet.Range("A1").CurrentRegion
    ' 绑定当前工作表的透视表存放起始位置
    Set rngDestPvt = WSheet.Range("H3")
    
    ' 检查当前表是否已存在同名透视表，存在则删除
    On Error Resume Next
    Set pvtTable = WSheet.PivotTables("LATE")
    On Error GoTo 0
    If Not pvtTable Is Nothing Then
        pvtTable.TableRange2.Delete
    End If
    
    ' 通过工作簿创建透视缓存（核心修正点）
    Set pvtCache = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=rngSrcData, _
        Version:=xlPivotTableVersion15 ' 用版本常量替代硬编码数字，提升兼容性
    )
    
    ' 创建透视表
    Set pvtTable = pvtCache.CreatePivotTable( _
        TableDestination:=rngDestPvt, _
        TableName:="LATE", _
        DefaultVersion:=xlPivotTableVersion15 _
    )
    
    ' 配置透视表字段
    With pvtTable.PivotFields("PAY CODE")
        .Orientation = xlPageField
        .Position = 1
        .ClearAllFilters
        .CurrentPage = "Late Clock In"
    End With
    
    With pvtTable.PivotFields("NAME")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    ' 添加计数数据字段
    pvtTable.AddDataField _
        Field:=pvtTable.PivotFields("PAY CODE"), _
        Caption:="Count of PAY CODE", _
        Function:=xlCount

Next WSheet

关键修正说明 #

• 把WSheet.PivotCaches.Create替换为ThisWorkbook.PivotCaches.Create，正确调用工作簿级别的透视缓存对象
• 所有Range对象均绑定到当前循环的WSheet，彻底避免数据源指向错误
• 添加同名透视表检查删除逻辑，防止重复命名报错
• 合并重复的PAY CODE字段设置代码，精简逻辑
• 使用Excel内置版本常量替代硬编码数字，适配不同Excel版本

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