Using Excel macros to combine multiple sheets is one of the ways to greatly enhance productivity by automating and speeding up the process, especially where the number of worksheets are many. This article is part of the 3 approaches we’re exploring. If you have not gone through the various approaches, follow these links:

Macros are not the easiest to code but on the other hand can offer loads of time savings as they help you to automate mundane tasks.

This example will focus on a school scenario.

We have data for various streams (4R, 4G, 4Y) of the fourth grade students in a fictitious school. The teacher in charge of the fourth grade receives the scores of the students from the 3 streams in Excel worksheets. He would want to combine these scores into a single worksheet, appending each list below the other.

This will help him to rank and grade all the students instead of having that information for each class separately.

The structure of the source worksheets is as shown below.

Using Excel Macros to Combine Multiple sheets

The macro needed should achieve the following:

  • Copy the range B4:Q5 once and paste the same in cells B4:Q5 in the combined worksheet.
  • Select the range of data from the first student to the last in each of the worksheet and paste into the combined worksheet as values
  • For subsequent worksheets, the macro should paste the data in the row just below where the first data reached
  • Against each of the data sets pasted, the macro should indicate in an adjacent column which stream/class the data belongs

Step #1: Open the Visual Basic Editor

The macro will be coded in the Visual Basic Editor of Excel. Press ALT+F11 to open the VBE.

On the VBE window, right click on the left pane >>Insert >>Module

Combine Multiple Sheets in Excel

Step #2: Type the Macro Code

Upon choosing Module, a window will open on the right side of the screen where you will paste the following code:


Step #3: Assign the Macro to a Button

Having tested the code and ensured it is working properly, we will then insert a shape in the worksheet labeled COMBINED and upon right clicking on the shape, we choose “Assign macro…” from the shortcut menu.

Also, add some text to the shape for guidance.

Final Thoughts…

With Excel macros, the choices are limitless.The example discussed here is one of the tens of ways to code the same problem!

If you have routine tasks that you would want to save time on, I encourage you to invest some hours every day to learn Visual Basic for Application (VBA) in Excel.

Macros have the potential to save you 4 days of work every month and reduce that to 1 hour or less!

Download the file used to interrogate the code more.

Tell a friend…

I believe this example is a time saver for you. Do you have colleagues who may be suffering from a similar challenge, share with them using any medium below.