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:

• Using the consolidate feature in Excel
• Working with the Power Query to combine worksheets

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.

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

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:

Sub CombineWorksheets()

    Dim DestinationSht As Worksheet 'This is the master worksheet where others will be combined
    Dim sht As Worksheet 'Object for handling worksheets in loop
    Dim LastDestRow As Long
    Dim SourceLastRow As Long 'this identifies last occupied row in the input worksheets
    Dim SourceLastColWithData As Long 'This is standard for this example
    Dim RangeToCopy As Range
    Dim ClassSize As Long 'Defines how many students are in a class
    
    'We don't want our program to slow down, thus we put off screen updating
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    Set DestinationSht = ThisWorkbook.Worksheets("Combined")
    With DestinationSht
        'Get column headers from first worksheet
        ThisWorkbook.Worksheets("4R").Range("B4:Q5").Copy _
        Destination:=DestinationSht.Range("B4:Q5")
        
        'We define the first row where data will be pasted in the destination worksheet
        LastDestRow = DestinationSht.Cells(DestinationSht.Rows.Count, "B").End(xlUp).Row
    End With
           
        'loop through all the worksheets
        For Each sht In ThisWorkbook.Worksheets
        
            If Left(sht.Name, 1) = 4 Then 'Our source worksheets start with a 4. We wish to skip other worksheets
            
                'Identify the last occupied row. We use column B that has the student names
                SourceLastRow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row 'MsgBox SourceLastRow //You can use this line to check whether the rows returned are correct
                
                SourceLastColWithData = 17 'In all the worksheets, column Q (= #17) is the last column with data
                
                    With sht
                        Set RangeToCopy = .Range(.Cells(6, 2), .Cells(SourceLastRow, SourceLastColWithData))
                        RangeToCopy.Copy _
                        Destination:=DestinationSht.Range("B" & LastDestRow + 1)
                        
                            DestinationSht.Range("A5").Value = "Class" 'Column heading to identify the data sets once pasted
                            
                            DestinationSht.Range("A" & LastDestRow + 1).Value = sht.Name
                            'Get the last used row in the adjacent column B in the destination sheet, and fill the data accordingly in column A
                            DestinationSht.Range("A" & LastDestRow + 1).AutoFill Destination:=Range("A" & LastDestRow + 1 & ":A" & Range("B" & Rows.Count).End(xlUp).Row)
                    End With
            End If
            LastDestRow = DestinationSht.Cells(DestinationSht.Rows.Count, "B").End(xlUp).Row
        Next sht
    
    ' Call AutoFit on the destination sheet so that all data is readable.
    DestinationSht.Columns.AutoFit
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

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.