Excel VBA Macro to Combine Worksheets in Excel

In this Excel tutorial, We will be sharing a VBA automation script I use (as well as some of my clients) to combine any number of worksheets into a master table.

If you use Microsoft Excel a lot, especially at work, it's a huge pain to combine multiple worksheets into a single master worksheet. Good thing we can automate just about any Excel work with VBA (aka Excel Macro). In this Excel tutorial, We will be sharing a VBA automation script I use (as well as some of my clients) to combine any number of worksheets into a master table.

Timestamp:

00:00 - Quick overview and demo 02:00 - Tutorial starts

Source Code:

Sub Combine_Worksheets()

    Dim LastRow As Long, LastColumn As Long, RowTracker As Long
    Dim wsMaster As Worksheet, ws As Worksheet
    Dim flag As Boolean

    '// Create Master Worksheet Object
    Set wsMaster = ThisWorkbook.Worksheets("Master")

    '// Clear Master Worksheet Content (Including the formatting)
    wsMaster.Cells.Clear

    RowTracker = 2
    flag = False

    '// Iterate each worksheet in this Excel file, except the Master Workshete
    For Each ws In ThisWorkbook.Worksheets
        If UCase(ws.Name) <> "MASTER" Then
            LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
            LastColumn = ws.Cells(1, Columns.Count).End(xlToLeft).Column

            If Not flag Then
                ws.Range("A1").Resize(1, LastColumn).Copy wsMaster.Range("A1")
                flag = True
            End If

            ws.Range(ws.Cells(2, 1), ws.Cells(LastRow, LastColumn)).Copy wsMaster.Cells(RowTracker, 1)

            RowTracker = RowTracker + LastRow - 1

        End If
    Next ws

    Call Formatting_Master_Sheet(wsMaster)

    MsgBox "Process is complete", vbInformation

    Set wsMaster = Nothing

End Sub

Private Sub Formatting_Master_Sheet(ByVal worksheet_object As Worksheet)

    Dim LastRow As Long, RowNumber As Long
    Dim LastColumn As Long

    With worksheet_object
        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        LastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column

        '// Insert comma seperator
        .Columns("D:G").NumberFormat = "#,##0"

        '// Convert URLs to Hyperlinks
        For RowNumber = 2 To LastRow
            .Hyperlinks.Add .Cells(RowNumber, "H"), Address:=.Cells(RowNumber, "H").Value, TextToDisplay:=.Cells(RowNumber, "B").Value
        Next RowNumber

        '// Applying Background Color To Alternate Rows
        For RowNumber = 2 To LastRow
            If RowNumber Mod 2 = 1 Then
                .Cells(RowNumber, "A").Resize(1, LastColumn).Interior.Color = RGB(230, 255, 227)
            End If
        Next RowNumber

        '// Makes sure all fonts are the same
        .Cells.Font.Size = 12
        .Cells.Font.Name = "Noto Sans"

        '// Autofit Columns
        .Cells.EntireColumn.AutoFit

        '// Format Table Headers
        .Cells(1, 1).Resize(1, LastColumn).Interior.Color = RGB(233, 233, 233)
        .Cells(1, 1).Resize(1, LastColumn).Font.Bold = True

        Application.Goto .Range("A1"), True

    End With

End Sub

Subscribe: https://www.youtube.com/channel/UCvVZ19DRSLIC2-RUOeWx8ug

excel excel-vba

In this MS Excel VBA video, we are going to see that how can we delete cells in excel using the VB code in the VBA editor and the Overview of formulas in Excel. We will see Basic Excel formulas & functions with examples .

In thi MS Excel VBA video, we are going to look at how can we use the VB code to clear up cells in MS Excel. The clear cells feature of MS Excel is used for the purpose. Using this feature we can easily clear cell contents, cell formatting, cell comments and hyperlinks as well

In this MS Excel VBA video, we are going to see how can we use the VB code to manipulate the Column Width of Cells in MS Excel. So we are going to use two methods and explore them in detail, along with this we aare also going to see that what are the possible values for the width of columns

In this Excel VBA video, we are going to see the usage of With Block in Excel VBA. Using with block, we can reuse and rewrite multiple code lines. Also we are going to look at the interior property in brief as well, which allows us to set background colors and background gradient as well

In this Excel VBA video, we are going to look at the usage of the Paste Special function in Excel using the VB code. Paste Special function helps us to use the paste function in different formats such as Pasting Cell Width, Pasting Text only, Pasting Cell Formats.