Consolidating two excel files
The data copy operation is not complete.” Exit Sub End Sub We have used the Workbook object to accomplish this task and also the Error handler to trap any errors that may come in case any file is missing. Select Loop Exit Sub Err H: Msg Box “It seems some file was missing.Finally, the values in the source range are copied into the corresponding range in the target workbook and the code moves to the next file in the array.
There is one master file (or sheet) which needs to be consolidated by pulling data from multiple source files containing raw data (having the same data structure). If you are new to VBA, Excel macros, go thru these links to learn more.
Select last Row = Last Row In One Column(str Start Cell Col Name) Cells(last Row 1, 1). Paste Special xl Paste Values, xl Paste Special Operation None Application. We start by assigning the workbook where we want to consolidate the date to the variable current WB by using the statement: After this a looping construct has been used to go through all the inputs provided one by one and open the workbooks, it has been assumed these workbooks to contain on the data that we need to copy hence I did not specify the source sheet name, however this can be easily added to this code to add more functionality.
The current code will display a message box when it is not able to open any file and will stop.
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.
I have also made use of UDF (user defined function) to find the last cell in the column that we specify.