从动态命名工作表向新工作簿拉取数据时的VBA运行时错误91及类型不匹配问题求助

看起来你遇到的核心问题是错误地在当前工作簿中查找月份工作表，而不是在目标工作簿Revenue Tracker.xlsx中，同时初始的工作表查找逻辑也存在漏洞，导致ws对象未被正确赋值，最终触发运行时错误91。下面我会拆解问题并给出修正后的完整代码：

问题分析 #

1. 工作表查找范围错误：你原来的循环For Each w In Worksheets是遍历当前运行代码的工作簿的工作表，而不是Revenue Tracker.xlsx中的工作表，这导致ws指向的对象和后续要引用的工作簿不匹配。
2. 未处理工作表不存在的情况：如果Revenue Tracker.xlsx中没有任何匹配的月份工作表，ws会保持Nothing状态，后续引用ws.Name必然报错。
3. 对象类型与引用逻辑混淆：虽然你声明了ws_mth为Workbook，但后续的工作表查找逻辑完全脱离了这个对象，导致引用错位。
4. ListObject行数计算不严谨：直接用Range.Rows.Count会包含表头，应该用DataBodyRange来获取数据区域的行数，同时要处理表为空的情况。

修正后的代码 #

Sub PullRevenueTrackerInfo()
    'Pull info from respective column into correct column on to Cost Loader
    Dim ws_mth As Workbook, ws_charges As Workbook
    Dim mapFromColumn As Variant, mapToColumn As Variant
    Dim lastRow As Long, i As Integer, nextCell As Long, arrCopy As Variant
    Dim tbl As ListObject
    Dim wsNames As Variant, targetWs As Worksheet, w As Worksheet, El As Variant
    
    ' 1. 先确认目标工作簿是否打开
    On Error Resume Next
    Set ws_mth = Workbooks("Revenue Tracker.xlsx")
    Set ws_charges = Workbooks("Cost Loader.xlsx")
    On Error GoTo 0
    
    If ws_mth Is Nothing Or ws_charges Is Nothing Then
        MsgBox "请确保Revenue Tracker.xlsx和Cost Loader.xlsx都已打开！", vbExclamation
        Exit Sub
    End If
    
    ' 2. 在Revenue Tracker.xlsx中查找动态月份工作表
    wsNames = Split("January,February,March,April,May,June,July,August,September,October,November,December", ",")
    Set targetWs = Nothing ' 初始化对象
    
    For Each w In ws_mth.Worksheets ' 遍历目标工作簿的工作表
        For Each El In wsNames
            If w.Name = El Then
                Set targetWs = w
                Exit For ' 找到匹配的工作表后退出内层循环
            End If
        Next El
        If Not targetWs Is Nothing Then Exit For ' 找到后直接退出外层循环，避免不必要的遍历
    Next w
    
    ' 检查是否找到目标工作表
    If targetWs Is Nothing Then
        MsgBox "在Revenue Tracker.xlsx中未找到匹配的月份工作表！", vbExclamation
        Exit Sub
    End If
    
    ' 3. 确认目标表中的ListObject存在
    On Error Resume Next
    Set tbl = targetWs.ListObjects("Table_owssvr")
    On Error GoTo 0
    
    If tbl Is Nothing Then
        MsgBox "目标工作表中未找到名为Table_owssvr的表格！", vbExclamation
        Exit Sub
    End If
    
    ' 检查表格是否有数据
    If tbl.DataBodyRange Is Nothing Then
        MsgBox "Table_owssvr表格中没有数据！", vbInformation
        Exit Sub
    End If
    
    ' 4. 定义列映射并复制数据
    mapFromColumn = Array("I", "J", "K", "L", "M", "N", "O", "P")
    mapToColumn = Array("A", "B", "C", "G", "K", "M", "H", "J")
    
    For i = 0 To UBound(mapFromColumn)
        ' 获取源列的数据区域（从第2行开始，即数据行）
        lastRow = tbl.DataBodyRange.Rows.Count + 1 ' 表头是第1行，数据从第2行开始，所以总数据行+1是最后一行的行号
        arrCopy = targetWs.Range(mapFromColumn(i) & 2 & ":" & mapFromColumn(i) & lastRow).Value
        
        ' 粘贴到Cost Loader的对应列
        With ws_charges.Worksheets(1)
            nextCell = .Range(mapToColumn(i) & .Rows.Count).End(xlUp).Row + 1
            .Range(mapToColumn(i) & nextCell).Resize(UBound(arrCopy, 1), UBound(arrCopy, 2)).Value = arrCopy
        End With
    Next i
    
    MsgBox "数据已成功导入！", vbInformation
End Sub

关键修改说明 #

• 修正工作表查找范围：将For Each w In Worksheets改为For Each w In ws_mth.Worksheets，确保在目标工作簿中查找月份表。
• 增加工作簿存在性检查：如果目标工作簿未打开，直接提示用户并退出，避免后续报错。
• 处理工作表未找到的情况：增加If targetWs Is Nothing的判断，给出明确提示，避免错误91。
• 优化ListObject处理：先确认表格存在，再检查是否有数据，避免空表导致的类型不匹配错误。
• 变量类型调整：将lastCell和nextCell改为Long类型，避免Excel行号超过Integer范围（Integer最大是32767，而Excel支持更多行）。
• 明确对象引用：直接使用targetWs引用找到的工作表，避免混淆不同工作簿的对象。

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