You can configure the following settings: Create a new worksheet for each file, or merge the data into one new sheet.
With this second option the data from the files will be placed below each other.
First, open Excel and open both of the files you're looking to merge. Now open a new spreadsheet, which will be the destination for your merged data.
From this new spreadsheet, click on the "Data" tab and click on "Consolidate." The Consolidate menu will pop up with Function and Reference fields.
For Function, choose "Sum" (this is for basic consolidation, see the link in Resources for more complex tasks). Then select one of the Excel spreadsheets you want to merge.
Sub Get Data() Dim str Where To Copy As String, str Start Cell Col Name As String Dim str List Sheet As Stringstr List Sheet = “List” On Error Go To Err H Sheets(str List Sheet). Select ‘this is the main loop, we will open the files one by one and copy their data into the masterdata sheet Set current WB = Active Workbook Do While Active Cell. If you want to learn how to develop applications like these and more, please consider joining our VBA Classes.
We then switch back to our main workbook and select the sheet where we want to paste the data, I have assigned this to the variable called “str Where To Copy”.
Open str File Name, Update Links:=False, Read Only:=True Set data WB = Active Workbook Range(str Copy Range). We open the data workbook by using the Application. Next we select the data that has been assigned to the copy range and copy to the clipboard.
I have also made use of UDF (user defined function) to find the last cell in the column that we specify.
This allows us to paste data onto separate sheets within the same workbook.