VBA Code to Merge worksheets from Excel files in 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 line of interest stop Excel from updating which we’re working:

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

We then 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, in a loop, copies each worksheet in the opened worksheet into the master workbook:

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!