Loop Through Files

Question

In row B I have a list of codes and in H2 I have a folder path that contains all the files for these listed codes.

I am trying to loop through this list, activate the corresponding file, copy and paste the values into their corresponding tab in the original file. Although I can not seem to get this code to work.

Can someone please tell me how to fix it?

Sub Master_Recipe()

Dim MainLoop As Integer
Dim WB As Workbook
Dim WBmain As Workbook
Dim Fac As Integer

MainLoop = 2
Set WBmain = ActiveWorkbook

    Do While MainLoop < 15
        Fac = Range("B" & MainLoop).Value
        Set WB = Range("H2").Value & Fac & " - Recipe Book" 'Object required error here

        Workbooks(WB).Activate
            Range("C:G").Copy

        Workbooks("WBmain").Activate
        Worksheets("Fac").Activate
            Range("C:G").Paste

        MainLoop = MainLoop + 1
    Loop

End Sub

Answer 1

There are quite a few issues to address (so I'll be updating the answer as you provide clarification).

Range without a defined sheet is VERY bad practice.

Sub Master_Recipe()

Dim MainLoop As Integer
Dim WB As Workbook
Dim WBmain As Workbook
Dim Fac As Integer

Set WBmain = Application.Workbooks.Open("WBmain")

    For MainLoop = 2 to 14
        Fac = WBMain.Sheets("NAME OF SHEET with Data").Range("B" & MainLoop).Value
        Set WB = Application.Workbooks.Open(WBMain.Sheets("NAME OF SHEET with Data").Range("H2").Value & Fac & " - Recipe Book")

        WB.Sheets("Name of sheet in workbook").Range("C:G").Copy
        WBMain.Sheets("Fac").Range("C:G").Paste

    Next MainLoop

End Sub