Apply a default header / footer using Custom Views
2004-06-07 Worksheets 0 74
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 SubCopy and paste the macro below into the ThisWorkbook module sheet:
Private Sub Workbook_BeforePrint(Cancel As Boolean) ApplyDefaultView End SubIn 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.