Macro Assistance - Help Trimming Code and Adding Header / Footer

[Justinian]

Can anyone help me to modify the following code? I recorded a simple macro to remove columns and reformat data and all I need added to this code is to make the data print in as few pages as possible while also including a header and footer. All I want for the header is "Completed Orders" and below that: =TODAY(); all I want for the footer is the page number. Ultimately, I need the code to format each page so all the data is perfectly centered while being as large as possible and having the header and footer still visible.

Here is the code, which runs fine, but needs to abovementioned adjustments:


Sub Format_Data()
'
' Format_Data Macro
'
'
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Selection.Delete Shift:=xlUp
Selection.Delete Shift:=xlUp
Selection.Delete Shift:=xlUp
Selection.Delete Shift:=xlUp
Selection.Delete Shift:=xlUp
Selection.Delete Shift:=xlUp
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Range("A1:E1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1").Select
Selection.FormulaR1C1 = "Number" & Chr(10) & "u"
With Selection.Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Characters(Start:=8, Length:=1).Font
.Name = "Marlett"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -2383463
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("B1").Select
Selection.FormulaR1C1 = "Location" & Chr(10) & "u"
With Selection.Characters(Start:=1, Length:=9).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Characters(Start:=10, Length:=1).Font
.Name = "Marlett"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -2383463
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("C1").Select
Selection.FormulaR1C1 = "Issue" & Chr(10) & "u"
With Selection.Characters(Start:=1, Length:=6).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Characters(Start:=7, Length:=1).Font
.Name = "Marlett"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -2383463
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("D1").Select
Selection.FormulaR1C1 = "Assigned to" & Chr(10) & "u"
With Selection.Characters(Start:=1, Length:=12).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Characters(Start:=13, Length:=1).Font
.Name = "Marlett"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("E1").Select
Selection.FormulaR1C1 = "Status" & Chr(10) & "u"
With Selection.Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Characters(Start:=8, Length:=1).Font
.Name = "Marlett"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -2383463
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Columns("A:A").ColumnWidth = 9.71
Columns("B:B").ColumnWidth = 8.86
Columns("C:C").ColumnWidth = 23.57
Columns("C:C").ColumnWidth = 25.14
Columns("D:D").ColumnWidth = 12.43
Columns("D:D").ColumnWidth = 14.57
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveWindow.SmallScroll Down:=-15
Range("A1:E1").Select
Selection.Font.Bold = False
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
Selection.Font.Bold = True
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:E" & Lastrow).Select
Range("A11").Activate
Selection.Subtotal GroupBy:=4, Function:=xlCount, TotalList:=Array(4), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Range("F3").Select
End Sub