For loop cannot detect the row and column

Question

My script can detect rows and column in first round but in the second round cannot detect rows and column.

It shows

"run-time error '1004' : "\" could not be found.

Is there some missing code or is the sequence I put wrong?

Sub Conso()

   ' Get common values, eg. path, date
    Call getValues

    Workbooks("Daily Reporting Template.xlsm").Activate
    Worksheets("Master").Activate

    'Open Staff Input Value
    Dim i As Integer
    'Dim j As Long
    'j = Cells(Rows.Count, 13).End(xlUp).Row
    For i = 7 To 30
        StaffPath = Cells(i, 4).Value
        Ws = Cells(i, 3).Value
        THPath = Cells(3, 2).Value
        wrkFold = Cells(2, 2).Value
        Filename = Cells(4, 2).Value

        'Open Template
        'Workbooks.Open Filename:= _
        '  wrkFold & "Master TH\Template\Daily Reporting Template.xlsm", UpdateLinks:=3
        Windows("Daily Reporting Template.xlsm").Activate

        Workbooks.Open Filename:= _
          StaffPath & "\" & Filename & ".xlsm", UpdateLinks:=3
        Cells.Select
        Selection.Copy

        Windows("Daily Reporting Template.xlsm").Activate
        Sheets(Ws).Activate
        Cells.Select
        ActiveSheet.Paste

        ActiveWorkbook.SaveAs Filename:= _
          THPath & "Daily Reporting Template" & ".xlsm" _
          , FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", _
          ReadOnlyRecommended:=False, CreateBackup:=False
        Application.DisplayAlerts = False

        Windows(Filename & ".xlsm").Close

    Next i

End Sub

Answer 1

Your code picks the value based on active sheet. This causes issues during Loop since it just refers to different file. Try the following code.

UnTested

Sub Conso()

Dim DailyRptTemplate As Workbook, MasterSht As Worksheet, TempWbk As Workbook

   ' Get common values, eg. path, date
    Call getValues

    Set DailyRptTemplate = Workbooks("Daily Reporting Template.xlsm")
    Set MasterSht = DailyRptTemplate.Worksheets("Master")

    Application.DisplayAlerts = False
    'Open Staff Input Value
    Dim i As Integer
    'Dim j As Long
    'j = Cells(Rows.Count, 13).End(xlUp).Row
    For i = 7 To 30
        StaffPath = MasterSht.MasterShtCells(i, 4).Value
        WS = MasterSht.Cells(i, 3).Value
        THPath = MasterSht.Cells(3, 2).Value
        wrkFold = MasterSht.Cells(2, 2).Value
        Filename = MasterSht.Cells(4, 2).Value

    'Open Template
    'Workbooks.Open Filename:= _
        'wrkFold & "Master TH\Template\Daily Reporting Template.xlsm", UpdateLinks:=3

    Set TempWbk = Workbooks.Open(Filename:=StaffPath & "\" & Filename & ".xlsm", UpdateLinks:=3)

        TempWbk.Cells.Copy
        DailyRptTemplate.Worksheets(WS).Range("A1").Paste

        DailyRptTemplate.SaveAs Filename:=THPath & "Daily Reporting Template" & ".xlsm" _
            , FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False
        TempWbk.Close (False)
    Next i
Application.DisplayAlerts = True
End Sub

It is also worth to have look at the below section since these are not looped through the procedure.

THPath = MasterSht.Cells(3, 2).Value
wrkFold = MasterSht.Cells(2, 2).Value
Filename = MasterSht.Cells(4, 2).Value