EXCEL/VBA - Macro to format a simple table (add rows on top, freezes panes, formats column names, adds auto-filter)

-->

Option Explicit

Public Sub FormatSheet()

   

    ' 1. Freeze Panes

    With ActiveWindow

        .SplitColumn = 0

        .SplitRow = 1

    End With

    ActiveWindow.FreezePanes = True

   

   

    ' 2. Format all cells

    Cells.Select

    With Selection

        '.Borders = xlNone

        .VerticalAlignment = xlBottom

        .WrapText = False

        .Orientation = 0

        .AddIndent = False

        .ShrinkToFit = False

        .ReadingOrder = xlContext

        .MergeCells = False

    End With

   

   

    ' 3. Add rows on top

    Rows("1:4").Select

    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

   

   

    ' 4. Format first row for title

    Rows("1:1").Select

    With Selection.Font

        .Name = "Calibri"

        .Size = 18

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

        .OutlineFont = False

        .Shadow = False

        .Underline = xlUnderlineStyleNone

        .ThemeColor = xlThemeColorLight1

        .TintAndShade = 0

        .ThemeFont = xlThemeFontMinor

    End With

    Range("A1").Select

    ActiveWindow.Zoom = 200

   

   

    ' 5. Alignment for column titles

    Rows("5:5").Select

    With Selection

        .HorizontalAlignment = xlCenter

        .VerticalAlignment = xlCenter

        .WrapText = True

        .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .ReadingOrder = xlContext

        .MergeCells = False

    End With

    

    ' 6. Auto-filter

    Selection.AutoFilter

    Range("A1").Select

End Sub