Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

More tips for printing

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
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.



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
        '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)
    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.

'the whole workbook

'all worksheets

'all sheets

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

'print all selected sheets

'only the activesheet

'only "Sheet1"

'print only the selection

'print range