VBA循环在另存文件并重新打开原文件后终止的解决方法

我写了一个宏用来拆分包含多个IC（岗位角色）名称和对应多工作表数据的文件，目标是给每个IC生成独立文件用于发送个人绩效统计数据。
代码逻辑是筛选删除非目标IC的数据，重复处理每个工作表，运行时没有报错，但因为另存为新文件后重新打开原文件，循环直接终止了，没法继续处理下一个IC。请问怎么让代码在重新打开原文件后继续执行循环？

原代码片段：

Sub Individual_IC_Filter()

Dim List_IC_Names As Range
Set List_IC_Names = Range("List_IC_Names")

ThisWorkbook.Sheets("Dashboard").Range("C10").Value = Range("C10").Value

For Each cell In List_IC_Names

    'Filter Dashboard
    ThisWorkbook.Sheets("Dashboard").Cells(2, 7).Value = cell.Value

        
    'Selects IC Name in IC-Level Table tab and deletes other ICs (This section of code is repeated for each tab in the spreadsheet)
    
    With ThisWorkbook.Sheets("IC-Level Table").Range("IC_Data")
        .AutoFilter Field:=2, Criteria1:="<>" & cell.Value, Operator:=xlFilterValues
    End With
    
    With ThisWorkbook.Sheets("IC-Level Table").Range("IC_Data")
        .Offset(1, 0).Resize(ThisWorkbook.Sheets("IC-Level Table").UsedRange.Rows.Count - 1).EntireRow.Delete
    End With
    
    With ThisWorkbook.Sheets("IC-Level Table").Range("IC_Data")
        .AutoFilter Field:=2, Criteria1:=cell.Value, Operator:=xlFilterValues
    End With

        'Save as new file
    FName = cell.value & " Dashboard - LATAM - " & Format(ThisWorkbook.Sheets("Dashboard").Range("AI3"), "YYYY MM DD") & ".xlsm"
    FName2 = "IC Dashboard - LATAM - " & Format(ThisWorkbook.Sheets("Dashboard").Range("AI3"), "YYYY MM DD") & ".xlsm"
    
    ActiveWorkbook.SaveAs Filename:= _
            "Y:\Financials\2022\IC Goals\Master Region Score Card Files\2022 09 26\Regional Cuts\LATAM\" & FName
    
    'Open original IC file

    Workbooks.Open "Y:\Financials\2022\IC Goals\Master Region Score Card Files\2022 09 26\" & FName2
    
    
    'Saves & close split IC workbook
    Workbooks(FName).Close savechanges:=Flase    

Next cell

End Sub

循环终止的核心原因：

• 执行SaveAs后，原工作簿的上下文被替换——ThisWorkbook变成了新保存的文件，而非最初的原文件
• 后续打开原文件的操作只是生成了新的工作簿实例，但原来的循环执行环境已经失效，导致循环无法继续

不要修改原工作簿再重新打开，而是每次循环复制原工作簿的副本，在副本里处理数据并保存，原工作簿保持原样，循环自然能持续执行。修改后的代码如下：

Sub Individual_IC_Filter()
    Dim icNames As Variant
    Dim i As Integer
    Dim wbOriginal As Workbook
    Dim wbCopy As Workbook
    Dim savePath As String
    Dim dateStr As String
    
    ' 缓存原工作簿和固定参数，避免循环中重复读取
    Set wbOriginal = ThisWorkbook
    dateStr = Format(wbOriginal.Sheets("Dashboard").Range("AI3"), "YYYY MM DD")
    savePath = "Y:\Financials\2022\IC Goals\Master Region Score Card Files\2022 09 26\Regional Cuts\LATAM\"
    
    ' 把IC名称读取到数组，避免原工作簿变化后引用失效
    icNames = wbOriginal.Range("List_IC_Names").Value
    
    ' 遍历每个IC
    For i = LBound(icNames, 1) To UBound(icNames, 1)
        Dim currentIC As String
        currentIC = icNames(i, 1)
        
        ' 复制原工作簿到新实例
        wbOriginal.Save ' 确保原工作簿保存最新状态
        Set wbCopy = Workbooks.Add
        wbOriginal.Sheets.Copy Before:=wbCopy.Sheets(1)
        Application.DisplayAlerts = False
        wbCopy.Sheets(wbCopy.Sheets.Count).Delete ' 删除新建工作簿自带的空白表
        Application.DisplayAlerts = True
        
        ' 在副本中处理Dashboard
        wbCopy.Sheets("Dashboard").Cells(2, 7).Value = currentIC
        wbCopy.Sheets("Dashboard").Range("C10").Value = wbCopy.Sheets("Dashboard").Range("C10").Value
        
        ' 在副本中处理IC-Level Table（其他工作表可按此逻辑复制）
        With wbCopy.Sheets("IC-Level Table").Range("IC_Data")
            .AutoFilter Field:=2, Criteria1:="<>" & currentIC, Operator:=xlFilterValues
            .Offset(1, 0).Resize(wbCopy.Sheets("IC-Level Table").UsedRange.Rows.Count - 1).EntireRow.Delete
            .AutoFilter Field:=2, Criteria1:=currentIC, Operator:=xlFilterValues
        End With
        
        ' 保存并关闭副本
        Dim fName As String
        fName = currentIC & " Dashboard - LATAM - " & dateStr & ".xlsm"
        wbCopy.SaveAs Filename:=savePath & fName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        wbCopy.Close SaveChanges:=False
    Next i
    
    MsgBox "所有IC文件已生成完成！"
End Sub

• 提前缓存数据：把IC名称、日期、保存路径提前读取，避免循环中重复依赖原工作簿
• 操作副本而非原文件：每次循环复制原工作簿的所有工作表到新工作簿，在副本里修改，原工作簿始终保持初始状态，不会中断循环
• 关闭警告：删除新建工作簿的空白表时关闭Excel警告，避免弹窗打断流程
• 明确文件格式：SaveAs时指定FileFormat:=xlOpenXMLWorkbookMacroEnabled，确保保存为启用宏的xlsm格式

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