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