遍历工作簿时VBA Range方法报错问题求助

核心错误原因 #

你遇到的Method 'Range of object '_Worksheet' failed错误，根源是在ws.Range(Cells(14, "A"), Cells(...))这段代码里，Cells没有绑定对应的工作表对象ws——VBA中如果不指定工作表，Cells默认指向当前活动工作表，和前面的ws.Range不属于同一个工作表对象，自然会触发对象不匹配的错误。

修正后的完整代码 #

Sub forEachECWS()
    Dim ws As Worksheet
    Dim myRangeL1 As Range
    Dim outputWs As Worksheet
    Dim totalRow As Variant
    Dim pasteStartRow As Long
    
    ' 提前绑定输出工作表，避免反复查找工作簿/工作表
    Set outputWs = Workbooks("Output Workbook.xlsx").Worksheets("Loader 1")
    
    For Each ws In ActiveWorkbook.Worksheets
        ' 明确指定要取消合并的是当前遍历工作表的单元格
        ws.Cells.UnMerge
        
        ' 先定位Total行，同时处理没找到的情况
        totalRow = Application.Match("Total", ws.Range("A:A"), 0)
        If IsError(totalRow) Then
            ' 如果当前表没找到Total，跳过该表并打印提示
            Debug.Print "工作表" & ws.Name & "未找到Total行，已跳过"
            GoTo NextSheet
        End If
        
        ' 给所有Cells加上ws.前缀，明确归属当前遍历的工作表
        Set myRangeL1 = ws.Range(ws.Cells(14, "A"), ws.Cells(totalRow - 1, "I"))
        
        ' 获取输出表的粘贴起始行（B列，空2行）
        pasteStartRow = outputWs.Cells(outputWs.Rows.Count, "B").End(xlUp).Row + 2
        
        ' 直接赋值替代Copy/Paste，更高效稳定
        outputWs.Cells(pasteStartRow, "B").Resize(myRangeL1.Rows.Count, myRangeL1.Columns.Count).Value = myRangeL1.Value
        
        ' 给对应A列写入原工作表名称，覆盖复制区域的所有行数
        outputWs.Cells(pasteStartRow, "A").Resize(myRangeL1.Rows.Count, 1).Value = ws.Name
        
NextSheet:
    Next ws
    
    Application.CutCopyMode = False
End Sub

关键修改点说明 #

• 绑定工作表对象：所有Cells、Range操作都明确指定所属工作表（比如ws.Cells、outputWs.Cells），彻底摆脱对活动工作表的依赖，这是解决你核心错误的关键。
• 处理Match错误：加入IsError(totalRow)判断，避免因某张表没有"Total"文本导致代码崩溃。
• 取消Activate操作：直接通过outputWs对象引用输出工作表，不用反复激活工作簿/工作表，代码更稳定高效。
• 优化复制逻辑：用直接赋值替代Copy/PasteSpecial，不仅速度更快，还能避免剪贴板冲突。
• 修正名称写入范围：确保原工作表名称写入到输出表A列，且覆盖复制区域的所有行数，不会出现范围不匹配的问题。

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