CODEWITHSUNDEEP
vbaexcel
Ori Meir
Ori Meir

Reputation: 51

Loop Through Files

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

Upvotes: 3

Views: 133

Answers (3)

OpiesDad
OpiesDad

Reputation: 3435

The problem is in the line you said:

Set WB = Range("H2").Value & Fac & " - Recipe Book"

WB is declared as a workbook, but you are trying to make it equal to a string.

What you want is:

Set WB = Application.Workbooks.Open(Range("H2").Value & Fac & " - Recipe Book")

assuming that the created string contains the complete path to the file.

If you do this, you will have problems with this line:

Workbooks(WB).Activate

This is because WB is declared as a workbook, but you are trying to use it as a sttring. You need to do either:

WB.Activate

or

Workbooks(Wb.Name).Activate

I think you intended on declaring WB as a string, in which case the only change you need is:

Dim WB As String

assuming the file is already open.

****EDIT****

If you do this, you need to get rid of the "Set" keyword, so the line should be:

WB = Range("H2").Value & Fac & " - Recipe Book"

Upvotes: 2

Chrismas007
Chrismas007

Reputation: 6105

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

Upvotes: 4

R3uK
R3uK

Reputation: 14547

You are trying to set a string as a workbook object, that is why you have the error :

I added a temp variable to show you where the problem come from :

Sub Master_Recipe()

Dim MainLoop As Integer, _
    WB As Workbook, _
    WBmain As Workbook, _
    Fac As Integer, _
    TpStrWb As String

MainLoop = 2
Set WBmain = ActiveWorkbook

    Do While MainLoop < 15
        Fac = WBmain.Sheets("Fac").Range("B" & MainLoop).Value
        '---------Changes here-------------
        TpStrWb = WBmain.Sheets("Fac").Range("H2").Value & Fac & " - Recipe Book"
        Set WB = Workbooks.Open(TpStrWb)
        '---------Changes here-------------

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

        WBmain.Sheets("Fac").Range("C1").Paste

        MainLoop = MainLoop + 1
    Loop

End Sub

Upvotes: 1

Related Questions