Print tips for Excel
1. Printing non-contiguous areas on one page
2. Hide rows, columns or cells when you print a sheet
3. Print odd and even pages
4. Print visible, Hidden or all worksheets
5. Do not print Header or Footer on all pages
6. Print the Last Save time in the Footer of every sheet you print
7. Printing a Workbook's Full Path in a Header or Footer (John WalkenBach)
8. Print every Worksheet with a value In cell A1
9. Print the same worksheet ? time with the number in cell, Header or Footer
10. Print Formulas
11. Insert Page Breaks every ? rows
12. One liners to Print a workbook, sheet, sheets, selection.....
Printing non-contiguous areas on one page
Insert a new worksheet (you're going to use this for printing).
Do this for each area you want to print on the same page.
(The areas don’t have to be on the same sheet)
If you change the original range, the picture will change too (values and formatting!).
You can use this sheet always to print because it will always update if you change
The original data.
Excel 97-2003
Select the range
Edit | Copy in the menu bar (or use Ctrl-C)
Go to the new worksheet and with the Shift key pressed click on Edit | Paste Picture Link.
Go back and do the same for the other areas.
Note: You see that the edit menu will change when you press the shift button when you click on Edit
Excel 2007
Select the range
Home tab | Clipboard group | Copy (or use Ctrl-C)
Go to the new worksheet and Click on the arrow on the Paste button in the Clipboard group on
the Home tab and choose As Picture >Paste Picture Link.
Go back and do the same for the other areas.
Print selection or range with one or more areas with a macro.
The macro will add a new sheet and copy all the selection areas on it.
Then it will print and delete the sheet.
Sub Test()
Dim Destrange As Range
Dim Smallrng As Range
Dim Newsh As Worksheet
Dim Ash As Worksheet
Dim Lr As Long
Application.ScreenUpdating = False
Set Ash = ActiveSheet
Set Newsh = Worksheets.Add
Ash.Select
Lr = 1
'You can also use a range with more areas like this
'For Each smallrng In Ash.Range("A1:C1,D10:G20,A30").Areas
For Each Smallrng In Selection.Areas
Smallrng.Copy
Set Destrange = Newsh.Cells(Lr, 1)
Destrange.PasteSpecial xlPasteValues
Destrange.PasteSpecial xlPasteFormats
Lr = Lr + Smallrng.Rows.Count
Next Smallrng
Newsh.Columns.AutoFit
Newsh.PrintOut
Application.DisplayAlerts = False
Newsh.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Hide rows, columns or cells when you print a sheet
AutoFilter or Advanced Filter is also a very good way to print only the things you want
in the sheet, filter the range with your criteria and print the sheet.
Check out this site for examples http://www.contextures.com/tiptech.html
But it is not always possible to get the result with a filter, See the examples below for
another way to hide Rows/Cells.
If you use one of the Print options in Excel the event below will check the ActiveSheet name
and run the code. This example will run if the ActiveSheet name = “Sheet1”
1) It will hide row 10:15
2) Print
3) Unhide row 10:15
Copy/Paste this event in the Thisworkbook module
Where do I paste the code that I want to use in my workbook
http://www.rondebruin.nl/code.htm
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet1" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
.Rows("10:15").EntireRow.Hidden = True
.PrintOut
.Rows("10:15").EntireRow.Hidden = False
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
Or hide columns (this example hide column B and D)
With ActiveSheet
.Range("B1,D1").EntireColumn.Hidden = True
.PrintOut
.Range("B1,D1").EntireColumn.Hidden = False
End With
Or hide all rows with a blank cell in column A
With ActiveSheet
On Error Resume Next
.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
.PrintOut
.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = False
On Error GoTo 0
End With
Make the Font white of a range
You can replace the red lines in the BeforePrint event above
with the example code below if you want to use it.
Or you can use the examples below in a macro.
1) It will make the font white
2) Print
3) Make the font black again
'Range with one area With ActiveSheet .Range("B10:B14").Font.ColorIndex = 2 .PrintOut .Range("B10:B14").Font.ColorIndex = 1 End With 'Range with more areas With ActiveSheet .Range("A1:A3,B10:B14,C12").Font.ColorIndex = 2 .PrintOut .Range("A1:A3,B10:B14,C12").Font.ColorIndex = 1 End With 'All cells with a error With ActiveSheet .Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Font.ColorIndex = 2 .PrintOut .Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Font.ColorIndex = 1 End With
Use conditional formatting
Make the Font white with conditional formatting
http://www.contextures.com/xlCondFormat03.html#Print
If you only want to print unprotected cells then use Dave Peterson's example below
Pick out a cell that you can use for a flag (say X1--but you can use any cell
you want, but keep it out of the print range).
Then select your range to hide/print.
Excel 97-2003: Format|Conditional formatting
Excel 2007: "Conditional formatting" In the Styles group on the Home tab
Click on New Rule and choose "Use a formula to determine which cells to format"
The formula is:
=AND($X$1="hide",CELL("Protect",A1)=1)
make it white font on white fill (or blue on blue or ...)
Note: A1 in the formula is the first cell in your selection
When you want to print, enter hide in X1 to hide, print and clear X1 when you're ready.
Hide Empty rows, Print and unhide the rows
This example will loop through every row in the range
Set rng = Sheets("Sheet1").Range("A1:A30")
If every cell in column A:G is empty it will hide that row.
After the loop it print the sheet and then unhide the rows.
Change "A1:G1" in the macro to the cells you want.
You can also use this with non contiguous ranges like "B1,D1:G1"
Sub Hide_Print_Unhide()
Dim rw As Long
Dim rng As Range
Dim cell As Range
Application.ScreenUpdating = False
Set rng = Sheets("Sheet1").Range("A1:A30")
With rng.Columns(1)
For Each cell In rng
If Application.WorksheetFunction.CountA( _
.Parent.Cells(cell.Row, 1).Range("A1:G1")) = 0 Then _
.Parent.Rows(cell.Row).Hidden = True
Next cell
.Parent.PrintOut
.EntireRow.Hidden = False
End With
Application.ScreenUpdating = True
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 visible, Hidden or all worksheets
If you want to print a whole workbook you can use this code line
ThisWorkbook.PrintOut Or ActiveWorkbook.PrintOut
But this will not print hidden Worksheets.
You can use this macro to print hidden and visible Worksheets
Sub Print_Hidden_And_Visible_Worksheets()
'Dave Peterson
Dim CurVis As Long
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
With sh
CurVis = .Visible
.Visible = xlSheetVisible
.PrintOut
.Visible = CurVis
End With
Next sh
End Sub
To print only hidden sheets use With Sh
CurVis = .Visible
If CurVis >= 0 Then
.Visible = xlSheetVisible
.PrintOut
.Visible = CurVis
End If
End With
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 these 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 thisActiveSheet.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 correct in Excel 97.
Where do I paste the code that I want to use in my workbook
http://www.rondebruin.nl/code.htm
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 every Worksheet with a value In cell A1
With this macro you loop through every worksheet and if there Is a
value in a certain cell it will add the sheet to the array and print it.
You can also test a for a word like Sh.Range("A1").Value = "PrintMe"
Sub Print_All_Worksheets_With_Value_In_A1()
Dim Sh As Worksheet
Dim Arr() As String
Dim N As Integer
N = 0
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Visible = xlSheetVisible And Sh.Range("A1").Value <> "" Then
N = N + 1
ReDim Preserve Arr(1 To N)
Arr(N) = Sh.Name
End If
Next
With ActiveWorkbook
.Worksheets(Arr).PrintOut
End With
End Sub
Print the same worksheet ? time with the number in cell, Header or Footer
This example will print ? copies of the same sheet (It use a Input box to ask you how many)
It will copy the page number in cell A1 or in the Header or Footer.
Note: you can use this for testing .PrintOut preview:=True
Sub PrintCopies_ActiveSheet_1()
Dim CopiesCount As Long
Dim CopieNumber As Long
CopiesCount = Application.InputBox("How many copies do you want", Type:=1)
For CopieNumber = 1 To CopiesCount
With ActiveSheet
' This example print the number in cell A1
.Range("A1").Value = CopieNumber & " of " & CopiesCount
'If you want the number in the footer use this line
'.PageSetup.LeftFooter = CopieNumber & " of " & CopiesCount
'Print the sheet
.PrintOut
End With
Next CopieNumber
End Sub
The example below continue printing where It left off, such as today you print
numbered pages 1-25 and the next time when you enter 10 in the input box it print 26-35.
Sub PrintCopies_ActiveSheet_2() ' This example print the number in cell A1 Dim CopiesCount As Long Dim CopieNumber As Long CopiesCount = Application.InputBox("How many copies do you want", Type:=1) With ActiveSheet If Not IsNumeric(.Range("A1").Value) Then .Range("A1").Value = 0 For CopieNumber = 1 To CopiesCount .Range("A1").Value = .Range("A1").Value + 1 'Print the sheet .PrintOut Next CopieNumber End With 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: "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
And David McRitchie's site
http://www.mvps.org/dmcritchie/excel/formula.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 a workbook, sheet, sheets, selection.....
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