"This is the way..." to merge data (multiple files into one) - PowerQuery? VBA? ...?

[CaptnAbraham]

Dear people of the world~

It is a questions surely asked many a times... and answered a few times.
I have received, from a third party, some 200+ inspection reports of apartments. Each apartment is one Excel file with one Sheet in them (but named differently).
The two photo snippets show the beginning and the end of one of the files.

My task would be to merge all the relevant / recorded data (yellow cells) into a table. Unfortunately this third party's report is not very data-friendly...
In my olden days I would...
a) open one of the files and copy the future header information (in this case column B mostly, but not exclusively, there are also sub-fields as well in the following columns this time around...) into a new file,
b) link the two files / data entry cells together into one row (here: yellow) for all x-hundred cells (into new columns)
c) copy the finished row and use the replace button to... replace the file name with the next one
d) repeat b)+c) for as many times as needed

This can't be the future... I have tried to use PowerQuery and I could load the files no problem... but it's a mess obviously. Cleaning it up, while I'm able to do basic things with normal data sets, is in this case over my head. Is it even possible? Would VBA be a better solution for this? It's close to 600 individual cells... I don't have the imagination how to even start on that (though I guess if the task for one cell is repeatable, I would have to simply replicate that code... 600 times... and be done with it?).

Any pointers on what to use to automate such a task? If an example then on how to do that is at your hands, that would be heavenly.

Cheers and thanks for reading through my ramble, haha.

 

[CaptnAbraham]

Alright... After four hours of searching I'm taking the first baby steps. Here is a part of it... it goes on a bit longer in its original state as there are 600 cells to copy.
With that I'm able to gather the information of one sheet.

Next step: Finding a way to change the filename and output-/range-row (so far still using the replace button to change the code).


Sub DataGathering()
'
Range("C4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R3C2"
Range("D4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R3C4"
Range("E4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R3C8"
Range("F4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R3C10"
Range("G4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R5C3"
Range("H4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R5C5"
Range("I4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R5C6"
Range("J4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R5C7"
Range("K4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R5C9"
Range("L4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R5C11"
Range("M4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R6C3"
Range("N4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R6C6"
Range("O4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R6C8"
Range("P4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R6C10"
Range("Q4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R7C3"
Range("R4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R7C6"
Range("S4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R7C9"
Range("T4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R8C3"
Range("U4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R10C3"
Range("V4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R10C6"
Range("W4").Select
Selection.FormulaR1C1 = "=[Filename.xlsx]EG_re!R10C8"
Range("X4").Select

End Sub