Loop macro - across the row then downwards

  • Hello forum genius! Just wondering if anyone can help with a loop for a macro?

    I have managed to scrape this together

    Sub OpenFile()
    Dim wkb As Workbook, wkbFrom As Workbook
    Dim fromPath As String
    '   Get path from cell A1 on Report tab
    fromPath = Sheets("Reports").Range("A1")
    Set wkb = ThisWorkbook
    Set wkbFrom = Workbooks.Open(fromPath & Range("B1").Value)

    I managed to get this to open the file listed in cell B1.

    However, I don't know how to loop this to then go across to C1, D1 until F1), of which some might be blank.

    Then I have to loop this downwards ie B2 ... F2.

    Can someone help?

  • Thanks for such a prompt reply! I am trying to get the macro to open those files and then merge all those open files as one file. I haven't quite figured out that part yet especially the save as filename using the value in J.

    My search attempts point me towards using PowerQuery but my version of Excel does not have this. Further to that, these files are located on a network drive and I need to send these merged files via email to people who do not have access to the network. I did the move worksheets manually ... which was doable with 10 workbooks but we are now having to do it with over 300 files ... I don't know if I should hand in my resignation or power up on coffee now...

  • That will still be slow even with a macro.

    Attach an example of the master workbook.

    Is the data in a table format? If so do you need to copy the header rows?

  • Hi Roy, thanks for being so quick.

    The data isn't in a table format.

    We get an excel extract like the one attached. I have to add other workbooks produced by other departments and then save a copy and send the merged copy.

    So I was hoping to get the macro to open B1, C1 all across - could be 3 or 4 documents depending on which would merge into the one then save it with the name of in J1. Then hop down to the next line for the next job.

  • Are you saying that you want to copy different columns to different workbooks? If so, what are the rules - which columns go to which workbook and where.

  • Oh my! !I'm so stupid! I thought you wanted to see the merging books looks like. I just realized what you meant! This is the structure that I'm working from (attached). The monthly extracts are like the above file.

    So far, I have managed to get the macro to open the very first file (Jan.xlsx). I need to open the files across (so in row 1, I need to have 4 files), merge all the tabs of those books together (plus the macro in that xlsm file) and then save the new file with name in J1 (JanExtract.xlsm)

    Then close those off, and move to the second row (which now has 5 files).

    I am looking at the loop syntax again, but have only managed to get even more confused.

  • It looks like Report contains a list of workbooks.

    Extract contains a table of data.

    Do you want to open each workbook listed and cpy the data to one sheet in the report workbook?

    Also, what do you mean by


    plus the macro in that xlsm file

  • I meant to copy it all to another (new) workbook. The report workbook I'm working from is basically a local copy that I use to track what I need to do, what I've done. They don't get the "Report" workbook which is what I work from.

    The macro in the xlsm file - this is a macro enabled workbook that another department gives us. There is a macro in it that is required. I have no clue what it does at all.

  • Actually Roy, you are right! They don't need any macro. I just had a look at the workbooks - not sure why it's xlsm when there is nothing there ...:/ I was told there was a macro. But when I actually opened the file, there isn't one. Maybe this is their way of stirring me up. Lesson learnt - double check things for myself now

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!