VBA宏二次迭代时活动工作表偏离原工作表的原因排查

核心原因 #

你的宏在循环里频繁打开新工作簿，还大量用Activate、Select操作，同时所有读取单元格的Range调用都没限定所属的工作簿和工作表。每次打开新文件后，Excel的活动工作表会自动切到新打开的工作簿，下一次循环执行Range("A2")、Range("B" & i)这类代码时，默认读的是当前活动的新工作簿里的表，而非宏所在的原工作簿初始表，这就导致VarCellValues读到错误的值。

虽然你定义了currwbk = ThisWorkbook，但没拿这个变量去限定读取原数据的Range操作，等于白定义了。

修复方案 #

彻底删掉Activate、Select这类依赖活动对象的操作，改用明确的对象引用指定工作簿和工作表，确保所有数据读取和操作都精准指向目标文件。

修改后的代码 #

Sub copy_financials_2022()
    Dim i As Integer
    Dim VarCellValue As String
    Dim VarCellValue2 As String
    Dim VarCellValue3 As String
    Dim VarCellValue4 As String
    Dim VarCellValue5 As String
    Dim currwbk As Workbook
    Dim sourceSheet As Worksheet ' 原工作簿的数据源表，替换成你实际的表名，比如Sheet1
    Dim targetWb As Workbook
    Dim consolidatedWb As Workbook
    Dim targetSheet As Worksheet
    Dim consolidatedSheet As Worksheet
    
    ' 绑定原工作簿和数据源表，明确指定，不再依赖活动对象
    Set currwbk = ThisWorkbook
    Set sourceSheet = currwbk.Sheets("Sheet1") ' 替换为你的原工作表名称
    
    Application.DisplayAlerts = False
    
    ' 循环范围限定为原工作簿数据源表的单元格
    For i = sourceSheet.Range("A2").Value To sourceSheet.Range("C2").Value
        ' 所有读取操作都限定在原工作簿的数据源表
        VarCellValue = sourceSheet.Range("B" & i).Value
        VarCellValue2 = sourceSheet.Range("C" & i).Value
        VarCellValue3 = sourceSheet.Range("A" & i).Value
        VarCellValue4 = sourceSheet.Range("D" & i).Value
        VarCellValue5 = sourceSheet.Range("E" & i).Value
        
        ' 打开目标工作簿并保存引用
        Set targetWb = Workbooks.Open(sourceSheet.Range("A3").Value & VarCellValue4 & ".xlsx")
        Set targetSheet = targetWb.Sheets(VarCellValue5)
        
        ' 直接操作目标表，无需Activate
        targetSheet.Unprotect Password:="forecast22"
        targetSheet.Columns("A:S").EntireColumn.Hidden = False
        
        ' 打开合并报表工作簿并保存引用
        Set consolidatedWb = Workbooks.Open("S:\Finance\_2022 FINANCIAL REPORTS\National Financials\09-30\CONSOLIDATED MONTHLY FINANCIAL STATEMENT.xlsm")
        Set consolidatedSheet = consolidatedWb.Sheets(VarCellValue2)
        
        ' 直接复制指定范围，无需Select
        consolidatedSheet.Range("A6:Q88").Copy
        targetSheet.Range("A6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
        ' 隐藏指定列，无需Select
        With targetSheet
            .Columns("B:B").EntireColumn.Hidden = True
            .Columns("D:E").EntireColumn.Hidden = True
            .Columns("G:G").EntireColumn.Hidden = True
            .Columns("I:J").EntireColumn.Hidden = True
            .Columns("L:N").EntireColumn.Hidden = True
            .Columns("K:K").EntireColumn.Hidden = True
            ' 写入公式
            .Range("C7").FormulaR1C1 = "Sept MTD"
            .Range("H7").FormulaR1C1 = "Sept YTD"
            .Range("S8").FormulaR1C1 = "Aug - Dec 2021"
            ' 保护工作表并保存
            .Protect Password:="forecast22"
        End With
        
        Application.CutCopyMode = False
        targetWb.Save
        targetWb.Close
        
        ' 关闭合并报表工作簿（如果不需要保留打开的话）
        consolidatedWb.Close SaveChanges:=False
    Next i
    
    Application.DisplayAlerts = True
End Sub

关键改动说明 #

1. 明确绑定原工作簿和数据源表：用sourceSheet变量指定宏所在的原工作表，所有读取循环参数和变量值的操作都通过这个变量限定，彻底避免读取到其他工作表的值。
2. 移除所有Activate和Select：直接通过工作簿、工作表变量操作单元格，不再依赖Excel的活动对象，从根源上杜绝活动工作表偏离的问题。
3. 保存打开的工作簿引用：用targetWb、consolidatedWb变量保存打开的工作簿对象，避免使用Workbooks(VarCellValue4)这种容易出错的方式（如果文件名重复会出问题）。

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