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

Access Beginner

Active Member
Joined
Nov 8, 2010
Messages
311
Office Version
  1. 2016
Platform
  1. Windows
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
Code:
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Access Beginner

Active Member
Joined
Nov 8, 2010
Messages
311
Office Version
  1. 2016
Platform
  1. Windows
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?

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,548
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top