Activesheet shifting away from original sheet on second iteration of the substatement-CodePudding

I can run this program one iteration at a time, but when I let it run on the next i, the VarCellValues come back as values from a different sheet. What would be causing the active sheet to change away from the workbook and first sheet the macro is opened from?

Sub copy_financials_2022()
'
' copy_financials_2022 Macro
'
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

Set currwbk = ThisWorkbook

For i = Range("A2").Value To Range("C2").Value

Set currwbk = ThisWorkbook

VarCellValue = Range("B" & i).Value
VarCellValue2 = Range("C" & i).Value
VarCellValue3 = Range("A" & i).Value
VarCellValue4 = Range("D" & i).Value
VarCellValue5 = Range("E" & i).Value

Application.DisplayAlerts = False



    Workbooks.Open (Range("A3").Value & VarCellValue4 & ".xlsx")
    
    'Workbooks.Open ("S:\Finance\_2022 FINANCIAL REPORTS\National Financials\09-30\CONSOLIDATED MONTHLY FINANCIAL STATEMENT" & ".xlsm")
    'Workbooks.Open (Range("B3").Value & VarCellValue4)
    
    Workbooks(VarCellValue4).Activate
    'inserted "Sheets(VarCellValue5).Activate" below after the third tab was active on Los Angeles Sheet (should have been the first tab)
    Sheets(VarCellValue5).Activate
    Sheets(VarCellValue5).Unprotect Password:="forecast22"
    Columns("A:S").Select
    Selection.EntireColumn.Hidden = False
    Workbooks.Open ("S:\Finance\_2022 FINANCIAL REPORTS\National Financials\09-30\CONSOLIDATED MONTHLY FINANCIAL STATEMENT" & ".xlsm")
    Sheets(VarCellValue2).Activate
    Range("A6:Q6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A6:Q88").Select
    Selection.Copy
    Workbooks(VarCellValue4).Activate
    Range("A6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B:B,D:E,G:G,I:J,L:N,K:K").Select
    Range("K1").Activate
    Selection.EntireColumn.Hidden = True
    Range("C7").Select
    'Range("A6").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Range("C7").Select
    ActiveCell.FormulaR1C1 = "Sept MTD"
    Range("H7").Select
    ActiveCell.FormulaR1C1 = "Sept YTD"
    Range("S8").Select
    ActiveCell.FormulaR1C1 = "Aug - Dec 2021"
    Range("A6").Select
    ActiveSheet.Protect Password:="forecast22"
    ActiveWorkbook.Save
    ActiveWindow.Close
    
    'Workbooks.Close ("S:\Finance\_2022 FINANCIAL REPORTS\National Financials\05-31\CONSOLIDATED MONTHLY FINANCIAL STATEMENT" & ".xlsm")
   
    
Next i

End Sub

CodePudding user response：

Instead of relying on a sheet being active, fully qualify each Range call with the appropriate workbook/worksheet.

Dim currwbk As Workbook
Set currwbk = ThisWorkbook

Dim currWs As Worksheet
Set currWs = currwbk.ActiveSheet

For i = currWs.Range("A2").Value To currWs.Range("C2").Value
    VarCellValue = currWs.Range("B" & i).Value
    VarCellValue2 = currWs.Range("C" & i).Value
    VarCellValue3 = currWs.Range("A" & i).Value
    VarCellValue4 = currWs.Range("D" & i).Value
    VarCellValue5 = currWs.Range("E" & i).Value

    Dim wb As Workbook
    Set wb = Workbooks.Open(currWs.Range("A3").Value & VarCellValue4 & ".xlsx")

    With wb.Worksheets(VarCellValue5)
        .Unprotect Password:="forecast22"
        .Columns("A:S").Hidden = False
 
        ' and so on
    End With
Next