Buy Me a Coffee? Your support is much appreciated!

PayPal Me:
Venmo: @Jie-Jenn

Source Code:

Const MasterSheetName As String = "Movie List"

Sub Combine_Movie_List()
    Dim wsMaster As Worksheet, ws As Worksheet
    Dim LastRow As Long, LastColumn As Long, RowInsert As Long
    Dim copyHeader As Boolean
    copyHeader = False '// Column Header has not been pasted
    RowInsert = 2
    Call DeleteMasterSheet
    Set wsMaster = ThisWorkbook.Worksheets.Add(ThisWorkbook.Worksheets(1))
    wsMaster.Name = MasterSheetName
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> MasterSheetName Then
            With ws
                LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
                LastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
                .Range(.Cells(2, 1), .Cells(LastRow, LastColumn)).Copy
                wsMaster.Cells(RowInsert, 1).PasteSpecial xlPasteValues ' Paste as Values Only
                RowInsert = RowInsert + LastRow - 1 ' -1 --> I do not want to include the header row
                If Not copyHeader Then
                    .Range("A1").Resize(1, LastColumn).Copy wsMaster.Range("A1")
                    copyHeader = True
                End If
            End With
        End If
    Next ws
    Call FormatTable(wsMaster)
End Sub

Private Sub DeleteMasterSheet()
    Application.DisplayAlerts = False
    On Error Resume Next
    Application.DisplayAlerts = True
End Sub

Private Sub FormatTable(ByVal worksheet_object As Worksheet)
    With worksheet_object
        '// Apply Cell Borders
        .Range("A1").CurrentRegion.Borders.LineStyle = xlContinuous
        '// Foramt the Headers
        .Range("A1").CurrentRegion.Rows(1).Font.Bold = True
        .Range("A1").CurrentRegion.Rows(1).Interior.Color = RGB(222, 222, 222) ' Light Gray Color
        '// Autofit Columns
        '// Insert Freeze Panes
        ActiveWindow.FreezePanes = True
        '// Set Focus Back to Range A1
        Application.Goto .Range("A1"), True
    End With
End Sub