This is a little more simplified
Usually you do not need to .Select some object to act on it
Just .Borders without an index applies to the outside 4
Option Explicit
Sub TestAddHeader()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
' Makes Sheet5the active sheet
With Sheets("Sheet5")
' Clears all formulas, text and row colours
.Cells.Clear
.Cells.ClearFormats
.Range("A1").Value = "Title 1"
.Range("B1").Value = "Title 2"
.Range("C1").Value = "Title 3"
.Range("D1").Value = "Title 4"
.Range("E1").Value = "Title 5"
.Range("F1").Value = "Title 6"
.Range("G1").Value = "Title 7"
.Range("H1").Value = "Title 8"
.Range("I1").Value = "Title 9"
With .Range("A1:I1")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
With .Font
.Bold = True
End With
With .Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
.Borders.LineStyle = xlContinuous
End With
.Rows("1:1").Select
' This applies the top row (Row 1) freeze
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
.FreezePanes = True
End With
.Range("A2").Select
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
A less brute force way would be something like
Range("A1").Resize(1, 9).Value = Array("Title 1", "Title 2", "Title 3", "Title 4", "Title 5", "Title 6", "Title 7", "Title 8", "Title 9")