Apply a default header / footer using Custom Views
2004-06-07 Worksheets 0 615
The macros below can be used to apply a default header/footer to a worksheet before it is printed. Any changes the user have made to the headers/footers will be overwritten and lost.
This might be useful since the headers/footers of a worksheet is not protected against changes when you apply the worksheet/workbook protection.
Create your workbook and set the headers/footers you want to include when the worksheets are printed.
Copy and paste the macros below into a normal module sheet:
Sub StoreDefaultViews()
Dim strCurSheet As String, ws As Worksheet, strView As String
If ActiveWorkbook Is Nothing Then Exit Sub
Application.ScreenUpdating = False
strCurSheet = ActiveSheet.Name
With ActiveWorkbook
For Each ws In .Worksheets
ws.Activate
strView = "DefaultView_" & ws.Name
On Error Resume Next
.CustomViews(strView).Delete
.CustomViews.Add strView, True, True
On Error GoTo 0
Next ws
End With
Sheets(strCurSheet).Activate
End Sub
Sub ApplyDefaultView()
If ActiveWorkbook Is Nothing Then Exit Sub
With ActiveSheet
On Error Resume Next
.Parent.CustomViews("DefaultView_" & .Name).Show
On Error GoTo 0
End With
End Sub
Copy and paste the macro below into the ThisWorkbook module sheet:Private Sub Workbook_BeforePrint(Cancel As Boolean)
ApplyDefaultView
End Sub
In Excel you can now press Alt+F8 and run the macro StoreDefaultViews to save the header/footer settings (and more).Every time the workbook/worksheets are printed (or previewed), the default view (included the headers/footers) will be applied.