Excel VBA多工作表合并至另一工作簿遇空表报错求助

需求与问题 #

• 需求：把data.xlsx里的Worksheet1到Worksheet4这4个工作表的前4列数据，合并复制到newdata.xlsm的Sheet1中，每个工作表的行数范围是0到60行（一般是0-20行）
• 问题：当data.xlsx里有空工作表时，宏会触发VBA Error 1004 – Application-Defined or Object-Defined Error，比如Worksheet2为空时，计算出的目标行号为0，Excel不允许写入第0行，导致报错

原代码的问题点 #

• 未判断工作表是否为空，直接计算有效行数，空表时的行号逻辑会导致后续目标行号异常
• 依赖前一个工作表的循环变量计算当前目标行，逻辑耦合严重，容易出错
• 逐单元格复制的方式效率低，且重复代码过多

兼容空表的优化代码 #

Sub CopyDataToNewFile()
    Dim sourceWB As Workbook
    Dim targetWB As Workbook
    Dim sourceWS As Worksheet
    Dim targetWS As Worksheet
    Dim lastRow As Long
    Dim targetRow As Long
    Dim wsNames As Variant
    Dim i As Integer
    
    ' 定义要处理的工作表名称数组
    wsNames = Array("Worksheet1", "Worksheet2", "Worksheet3", "Worksheet4")
    
    ' 打开源文件，获取目标文件对象（确保newdata.xlsm已打开）
    Set sourceWB = Workbooks.Open(Filename:=ThisWorkbook.Path & "\data.xlsx")
    Set targetWB = Workbooks("newdata.xlsm")
    Set targetWS = targetWB.Worksheets("Sheet1")
    
    ' 初始化目标起始行（从第2行开始，若需从第1行改为1）
    targetRow = 2
    
    ' 遍历每个工作表
    For i = LBound(wsNames) To UBound(wsNames)
        Set sourceWS = sourceWB.Worksheets(wsNames(i))
        
        ' 获取源工作表有效行数，处理空表报错情况
        On Error Resume Next
        lastRow = sourceWS.Cells(sourceWS.Rows.Count, 1).End(xlUp).Row
        On Error GoTo 0
        
        ' 若工作表为空（有效行数<2，假设第1行是表头），直接跳过
        If lastRow < 2 Then
            GoTo NextSheet
        End If
        
        ' 批量复制前4列数据，替代逐单元格复制提升效率
        sourceWS.Range(sourceWS.Cells(2, 1), sourceWS.Cells(lastRow, 4)).Copy _
            Destination:=targetWS.Cells(targetRow, 1)
        
        ' 更新目标行号，跳过已复制的行数
        targetRow = targetRow + (lastRow - 1)
        
NextSheet:
    Next i
    
    ' 关闭源文件，不保存修改
    sourceWB.Close SaveChanges:=False
    
    ' 释放对象内存
    Set sourceWS = Nothing
    Set targetWS = Nothing
    Set sourceWB = Nothing
    Set targetWB = Nothing
End Sub

代码关键点说明 #

• 用数组存储工作表名称，简化循环逻辑
• 加入空表判断：有效行数小于2时直接跳过该工作表
• 采用批量复制替代逐单元格复制，大幅提升执行效率
• 用独立的targetRow变量跟踪目标位置，避免依赖前序循环变量，逻辑更清晰
• 加入错误捕获，避免空表时获取行数触发报错

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