More tips for printing

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse

Do not print Header or Footer on all pages

The example below will only print the right header on the first page of the ActiveSheet.

You have the following options
(LeftHeader, CenterHeader, RightHeader, LeftFooter, CenterFooter, RightFooter)

Check out the VBA help for all formatting codes.Look for "Formatting Codes for Headers and Footers"
You can use something like this :

.CenterFooter = "&8Page &P & of &N"
.RightFooter = "&8Last Saved : &B" & ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
.LeftFooter = "&8" & ActiveWorkbook.FullName & Chr(10) & "Sheetname : &B" & ActiveSheet.Name

Sub Test()
    Dim TotPages As Long
    TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
    With ActiveSheet.PageSetup
        .RightHeader = "Your Header info"
        ActiveSheet.PrintOut From:=1, To:=1
        .RightHeader = ""
        ActiveSheet.PrintOut From:=2, To:=TotPages
    End With
End Sub

Tip: If you not want to print the last page you can do this
ActiveSheet.PrintOut From:=2, To:=TotPages -1

You can also make a different header for the last page if you want

Print the Last Save time in the Footer of every sheet you print

If you copy this in the ThisWorkbook module it will print the Last Save Time in the Right Footer of every sheet when you use one of the Print options in Excel. Note: The Property is not working ok in Excel 97.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim wkSht As Worksheet
    For Each wkSht In ThisWorkbook.Worksheets
        wkSht.PageSetup.RightFooter = "&8Last Saved : " & _
            Format(ThisWorkbook.BuiltinDocumentProperties("Last Save Time"), _
            "yyyy-mmm-dd hh:mm:ss")
    Next wkSht
End Sub

If you use Excel 97 you can use this to add the Date in a worksheet cell

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
                                Cancel As Boolean)
    'If you save the file the date/time will be placed in cell A1 of Sheet1
    Sheets("Sheet1").Range("A1").Value = Format(Now, "yyyy-mmm-dd hh:mm:ss")
End Sub

Print odd and even pages

This option is not available in Excel but you can use a macro to do it.

Sub Print_Odd_Even()
    Dim Totalpages As Long
    Dim StartPage As Long
    Dim Page As Integer

    StartPage = 1  '1 = Odd and 2 = Even

    'Or use the InputBox suggestion from Gord Dibben
    'StartPage = InputBox("Enter 1 for Odd, 2 for Even")

    Totalpages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
    For Page = StartPage To Totalpages Step 2
        ActiveSheet.PrintOut from:=Page, To:=Page, _
                             Copies:=1, Collate:=True
    Next
End Sub

Print Formulas

If you want to print your formulas then you can toggle the view with this

Excel 97-2003: Tools - Options - View and check Formulas
Excel 2007-2013: "Show Formulas" in the Formula Auditing group on the Formula tab

Or the shortcut CTRL ` in all Excel versions

Check out also this example from John Walkenbach.
http://j-walk.com/ss/excel/tips/tip37.htm

Insert Page Breaks every ? rows

If row 1 is a header row and you want to print it on every page then change RW + 1 to RW + 2 and use File>Page Setup>Sheet to fill in $1:$1 in the "Rows to repeat at top: " box.

This example will add breaks every 20 rows from row 1 till the last row with data in column A.

Sub Insert_PageBreaks()
    Dim Lastrow As Long
    Dim Row_Index As Long
    Dim RW As Long

    'How many rows do you want between each page break
    RW = 20
    
    With ActiveSheet
        'Remove all PageBreaks
        .ResetAllPageBreaks
        
        'Search for the last row with data in Column A
        Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
        
        For Row_Index = RW + 1 To Lastrow Step RW
            .HPageBreaks.Add Before:=.Cells(Row_Index, 1)
        Next
    End With
End Sub

One liners to Print

Look in the VBA help for PrintOut and see that you can use the following arguments.
expression.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName)

Note: Remember that you can't print sheets that are hidden. If you print the whole workbook with the first example there is no problem but the example for Sheets or worksheets (example 2 and 3) will not work if there are hidden sheets.

ActiveWorkbook.PrintOut
'the whole workbook

Worksheets.PrintOut
'all worksheets

Sheets.PrintOut
'all sheets

Sheets(Array("Sheet1", "Sheet3")).PrintOut
'all sheets in the array

ActiveWindow.SelectedSheets.PrintOut
'print all selected sheets

ActiveSheet.PrintOut
'only the activesheet

Sheets("Sheet1").PrintOut
'only "Sheet1"

Selection.PrintOut
'print only the selection

Range("C1:D5").PrintOut
'print range

Ron de BruinExcel Automation