VBA to Copy from Multiple workbooks into one sheet, Headers are all the same.

[Access Beginner]

Hi again,

I will have 25 Excel files all stored in the one folder. Each file will have headers commencing on A9 and will be will end at column F. All 25 files with have the same headers and these headers will be in the destination file (Reports.xlsm). The sheet I would like this copied to is called Data

I have looked at a few examples here and on the net, but haven't found one that meets my needs.

I will have a file called Reports.xlsm, can someone write some code to get data from cell A9 out to F9 and down till the last row with values in it and paste it into Reports.xlsm from A2. Then go to the next file and copy from cell A9 out to F9 and down till the last row with values in it and paste it into Reports.xlsm, from after the previously copied data and so on.

I have recorded a macro that kinda does what I want, but don't know how to get the last row with no values to paste the next copied lot of data

Sub GetData()
'
' GetData Macro
'
'
    Windows("Test1.xlsx").Activate
    Range("A9:G12").Select
    Selection.Copy
    Windows("Report.xlsm").Activate
    Range("A2").Select
    ActiveSheet.Paste
    
End Sub

 

[Access Beginner]

Hi,

I've got this to work, although as mentioned above this has to be done for 25 files. Can someone please tweak this to make it more efficient, so I do have to repeat this 23 more times?

Sub GetData()
'
' GetData Macro
'
'
'Finalrow = Cells(Rows.Count, 1).End(xlUp).Row
'Deletes the the data off the MasterData Sheet
    Sheets("MasterData").Select
    Cells.Select
    Selection.ClearContents

'Opens Test File 1 spreadsheet
    Windows("Test File 1.xlsx").Activate
'Selects the Header and data and copies this
    Range("A8").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
'Goes back the Reports spreadsheet and pastes the copied data above including headers on the MasterData sheet
    Windows("Master Report.xlsm").Activate
    Sheets("MasterData").Select
    Range("A50000").End(xlUp).Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    
'Copy From Here and repeat the code below for the remaining staff
'Opens Test File 2 spreadsheet
     Windows("Test File 2.xlsx").Activate
'Selects the data and copies this, the headers are not included
     Range("A9").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
'Goes back the Reports spreadsheet and pastes the copied data above excluding headers on the MasterData sheet
    Windows("Master Report.xlsm").Activate
    Sheets("MasterData").Select
     Range("A50000").End(xlUp).Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     
    
End Sub