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...

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.

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.

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