VBA code to merge all Excel files from a specific folder

The code

What the code does

The first part of the subroutine is simply declaring the variables which will be used throughout. This line is where it starts to get interesting:

This will ensure that if an error occurs while the subroutine runs, it will be caught and the code after the ErrMsg: label will be executed:

This simply checks that there is an error (I.e. Err.Number is not 0) then shows a message with the description of the error.

The next 2 lines of interest stop Excel from updating which we’re working and set a flag to indicate that the first line of each spreadsheet contains the headers:

Now, to the guts of the subroutine. We’re using the FileSystemObject to get the directory that contains the Excel files to merge:

We then assign the active worksheet in the master spreadsheet to a local variable for ease of use:

Next, we select each file in the folder and process it in a loop:

Within the loop, the code opens the spreadsheet in Read Only mode:

Then copies the rows which contain data, skipping the first row if it contains headers on any spreadsheet except the first (we need the headers from the first spreadsheet):

Now the code finds the last row containing data in our master spreadsheet by starting at the last possible row and moving up to the first used row it finds in column A (the maximum number of rows depends on the version of Excel but the code checks for that). ‘Paste after the last used cell in the master spreadsheet

We then skip to the next empty row (using Offset(1,0) ) and use the PasteSpecial method to paste the copied rows

‘Only offset by 1 if there are current rows with data in them If thisSheet.UsedRange.Rows.Count > 1 Or Application.CountA(thisSheet.Rows(1)) Then

Once the code has looped over all the spreadsheets, we save the master spreadsheet and ensure the workbook variable is emptied:

We then close the workbooks previously opened and re-enable updates in the Excel user interface:

That’s it!