VBA宏出现下标越界错误：需将Lot文件数据按工作表追加至Master文件

问题描述 #

编写VBA宏实现将Lot文件的工作表数据按名称追加到Master文件（同名表追加数据，无对应表则新建），但运行时在新建工作表的代码行触发**Subscript out of range（下标越界）**错误。测试场景：

• lot1.xlsx：包含Sheet1、Sheet2、Sheet3，各表有3条示例数据
• TF Master.xlsx：初始为空工作簿

错误原因分析 #

1. Workbooks集合引用错误：代码中用Workbooks(masterPath)引用Master工作簿，但Workbooks的索引只能是文件名（如"Tf Master.xlsx"）或工作簿序号，不能用完整文件路径。若Master未提前打开，这个引用会直接抛出下标越界错误。
2. 工作表归属对象缺失：新建工作表时Worksheets.Add(...)未指定所属的Master工作簿，默认会在当前活动工作簿（即lot1.xlsx）中创建，完全违背需求逻辑，同时也会导致后续引用Master工作簿时出错。

修复后的完整代码 #

Sub AppendDataFromLotToMaster()
    ' 定义文件路径
    Dim lotPath As String
    Dim masterPath As String
    lotPath = "somepath\Merge\lot1.xlsx"
    masterPath = "somepath\Tf Master.xlsx"

    ' 声明工作簿对象
    Dim lotWb As Workbook
    Dim masterWb As Workbook
    
    ' 打开两个工作簿并赋值给对象变量
    Set lotWb = Workbooks.Open(lotPath)
    Set masterWb = Workbooks.Open(masterPath)

    ' 遍历Lot文件的每个工作表
    Dim lotWs As Worksheet
    For Each lotWs In lotWb.Worksheets
        Dim masterWs As Worksheet
        
        ' 检查Master中是否存在同名工作表
        On Error Resume Next
        Set masterWs = masterWb.Worksheets(lotWs.Name)
        On Error GoTo 0
        
        ' 不存在则新建
        If masterWs Is Nothing Then
            Set masterWs = masterWb.Worksheets.Add(after:=masterWb.Worksheets(masterWb.Worksheets.Count))
            masterWs.Name = lotWs.Name
        End If

        ' 获取Master工作表的最后一行（处理空表情况）
        Dim lastRow As Long
        lastRow = masterWs.Cells(masterWs.Rows.Count, 1).End(xlUp).Row
        
        ' 复制数据：如果是新建表则复制全部（含表头），否则跳过表头从第2行开始复制
        Dim copyRange As Range
        If lastRow = 0 Then
            ' 空表，复制全部数据
            Set copyRange = lotWs.UsedRange
        Else
            ' 已有数据，跳过表头复制内容
            Set copyRange = lotWs.Range("A2", lotWs.Cells(lotWs.UsedRange.Rows.Count, lotWs.UsedRange.Columns.Count))
        End If
        
        ' 粘贴数据到Master工作表
        copyRange.Copy
        masterWs.Cells(lastRow + 1, 1).PasteSpecial xlPasteValuesAndNumberFormats
        
        ' 清除剪贴板，避免弹窗
        Application.CutCopyMode = False
    Next lotWs

    ' 关闭工作簿，Lot文件不保存修改
    lotWb.Close SaveChanges:=False
    ' 保存Master文件并关闭
    masterWb.Close SaveChanges:=True
End Sub

关键修改点 #

• 明确工作簿对象引用：将Master工作簿打开后赋值给masterWb变量，所有操作均通过该变量引用，避免路径或活动工作簿的歧义。
• 指定工作表归属：新建工作表时用masterWb.Worksheets.Add(...)明确指定在Master工作簿中创建，同时after参数也通过masterWb.Worksheets(...)指定位置。
• 处理空表场景：当Master工作表为空时（lastRow=0），直接复制全部数据（含表头）；已有数据时跳过表头，避免重复粘贴表头。
• 优化剪贴板操作：添加Application.CutCopyMode = False清除剪贴板，防止后续操作出现粘贴弹窗。

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