Printing certain sheets in an Excel file

Speed up printing

If you need to print certain sheets in a file in one step you can set up a macro to do it automatically and flexibly.

You can use a naming convention on those sheets you want to print. Rename the sheets you want to print with a prefix P_. Then have a macro go through all the sheets and print those sheets that start with P_.

That way it’s as easy as renaming the sheet to control whether it prints.

The macro below will print all sheet names starting with P_.

Sub PrintSheets()
 
Dim ws As Worksheet
 
For Each ws In Worksheets
 
  If Left(ws.Name, 2) = "P_" Then
    ws.PrintOut
  End If
 
Next ws
 
Set ws = Nothing
 
End Sub

It will start with the sheet tabs at the left of the screen and work its way to the right of the screen. This allows you to can change the order of the printing by moving the sheets around.

This macro could be amended to print sheet tabs with a certain colour. The trick with that technique is identifying the correct colour.

In Excel colours are defined by numbers. So you need to identify the correct number of the colour you use. Recording a macro of changing the tab colour will identify the colour’s number.

To see instructions on how to install and use macros see this blog post.

Click here to see how you can run a Macro off a Quick Access Toolbar icon.

Please note: I reserve the right to delete comments that are offensive or off-topic.