求助：编写VBA宏遍历指定外工作表并在主表引用指定单元格

需求回顾 #

• 遍历除「Mastersheet」「Conversions」「Dates」外的所有工作表（最多200个）
• 将每个公司工作表的A1单元格引用，依次写入主表「Mastersheet」的A2、A3……A(X+1)位置

修正后的完整代码 #

Sub ImportCompanyData()
    Dim ws As Worksheet
    Dim excludeSheets As Variant
    Dim masterWs As Worksheet
    Dim currentRow As Long
    
    ' 初始化需要排除的工作表列表
    excludeSheets = Array("Mastersheet", "Conversions", "Dates")
    ' 直接绑定主表对象，避免使用Activate
    Set masterWs = ThisWorkbook.Worksheets("Mastersheet")
    ' 从主表A2行开始写入数据
    currentRow = 2
    
    ' 遍历工作簿内所有工作表
    For Each ws In ThisWorkbook.Worksheets
        ' 检查当前工作表不在排除列表，且未超过200个公司的数量限制
        If IsError(Application.Match(ws.Name, excludeSheets, 0)) And currentRow <= 201 Then
            ' 生成单元格引用公式，保证主表数据随公司表实时更新
            masterWs.Cells(currentRow, "A").Formula = "='" & ws.Name & "'!A1"
            ' 移动到下一行
            currentRow = currentRow + 1
        End If
    Next ws
End Sub

关键改进说明 #

• 摒弃Activate/Select操作：直接通过工作表对象进行读写，减少运行时错误，提升代码执行效率
• 安全的列表校验：用IsError(Application.Match(...))判断工作表是否需要排除，避免Match找不到匹配项时抛出错误
• 数量限制控制：通过currentRow <= 201确保最多写入200条数据（对应主表A2到A201区域）
• 动态生成引用公式：通过字符串拼接生成标准的单元格引用公式，保证主表数据与源工作表实时同步

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