VBA code for using a form to choose Excel files to merge

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 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).

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

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!