Print code Examples

Note: In Excel for windows you can use this for testing .PrintOut preview:=True in the code examples to check out what will be send to the printer, but this is not working in Mac Excel. Also From:=1, To:=3 and Copies:=2 is not working in Mac Excel and will print the whole worksheet or just one copy, in the Oneliners section on the bottom of this page you see that there are more problems.

Note: check out also this page : Hide Data Print and Unhide with the BeforePrint event

Print visible 0r visible and Hidden worksheets


If you want to print a whole workbook you can use ThisWorkbook.PrintOut Or ActiveWorkbook.PrintOut in Excel for Windows but this will not print hidden Worksheets. And on the Mac it will only print the ActiveSheet so this is no option for printing the whole workbook in Mac Excel with VBA code. You can use this macro examples below to print what you want.


Sub Print_Visible_Worksheets()
Dim Sh As Worksheet
Dim Arr() As String
Dim N As Integer
N = 0
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Visible = xlSheetVisible Then
N = N + 1
ReDim Preserve Arr(1 To N)
Arr(N) = Sh.Name
End If
Next
If N = 0 Then Exit Sub
With ActiveWorkbook
.Worksheets(Arr).PrintOut
End With
End Sub

Sub Print_Hidden_And_Visible_Worksheets()
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


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
If N = 0 Then Exit Sub
With ActiveWorkbook
.Worksheets(Arr).PrintOut
End With
End Sub


Print the same worksheet ? time with the number in cell or 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.


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 enter 25 in the input box it 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


Oneliners to print


There are a few problems with the code lines below in Mac Excel, read the comments below the code line.


ThisWorkbook.PrintOut
ActiveWorkbook.PrintOut
'Print the whole workbook not working on Mac, it print the activesheet

Worksheets.PrintOut
'Print all worksheets but It will Group all worksheets if the first sheet is active
'Not working if you have hidden sheets in the workbook

'Sheets.PrintOut
'Print all sheets but It will group all sheets if the first sheet is active
'Not working if you have hidden sheets in the workbook

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

ActiveWindow.SelectedSheets.PrintOut
'Print all selected sheets

ActiveSheet.PrintOut
'Print only the activesheet

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

Selection.PrintOut
'Print only the selection Not working on Mac, print the whole sheet

Range("C1:C5").PrintOut
'Print range Not working on Mac, print the whole sheet
31-July-2021
Web design by Will Woodgate