Disclaimer: I am a long-time user of Excel, but am relatively new to VBA and working on improving my coding efficiency.
I am working on a template workbook intended for wide distribution to many users that are infrequent users of Excel. The workbook and all worksheets are passowrd protected to prevent damage by users. The workbook contains several dozen worksheets, nearly half of which are hidden (the "Printer-friendly" grayscale versions of their visible counterparts). I would like to create a print macro that would select a certain portion of these hidden sheets based on whether or not they have been identified as "Active" for any paticular condition. In other words, some of the hidden sheets will contain data and other will not. I would like the macro to create a single print job, as I would like it to most often print to a PDF. The totally inefficient way (rookie) way I have it set up now uses an IF statement for each sheet, creating a separate 2-page print job each time the condition is true. An abbreviated version of the macro I am using now follows:
Sub Print_Task_Sheets()
ActiveWorkbook.Unprotect Password:="fred"
Application.ScreenUpdating = False
Sheets("Setup 1").Select
If Range("FB24") > 0 Then
Sheets("p01").Visible = True
Sheets("p01").PrintOut Copies:=1, Collate:=True
Sheets("p01").Visible = False
End If
If Range("FB25") > 0 Then
Sheets("p02").Visible = True
Sheets("p02").PrintOut Copies:=1, Collate:=True
Sheets("p02").Visible = False
End If
If Range("FB26") > 0 Then
Sheets("p03").Visible = True
Sheets("p03").PrintOut Copies:=1, Collate:=True
Sheets("p03").Visible = False
End If
Application.ScreenUpdating = True
ActiveWorkbook.Protect Password:="fred"
End Sub
Bookmarks