VBA Code for using a form to merge Excel worksheets

The code

What the code does

There are several subroutines and a function in the VBA code we have used. In some of the subroutines, you will see the following at the beginning:

and this at the end:

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 which 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 code start with the AddFilesButton_Click()  subroutine. This code is executed when the Add Excel Files button is clicked. It starts off by showing a file choice dialog to allow the user to select the Excel files they would like to merge:
‘Let the user choose the files they want to merge

You’ll notice the strange looking #If Mac Then #Else #End If  section. This is in there because there is a different mechanism when running Excel on an Apple Mac to when running Excel on Windows. On a Mac we use the Select_File_Or_Files_Mac() function to choose the files. This function is declared at the end of the code and we won’t cover it here. If you are interested in this function, you can go to the link shown in the comments above the declaration.

Once the user has chosen the files, we do some simple checks that there were some files chosen and then we add them to the list box and enable the Merge button:

The CancelButton_Click()  subroutine is executed when the cancel button is clicked and simply closes the form with the following code:

Things start to get interesting in the MergeButton_Click()  subroutine! This is executed when the Merge button is clicked.

The first 2 lines of interest stop Excel from updating which we’re working and assign the active worksheet in the master spreadsheet to a local variable for ease of use:

We then select each file that was added to the list box 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, phew!