Results 1 to 2 of 2

Adding worksheets to a group for printing

Threaded View

  1. #1
    Registered User
    Join Date
    03-01-2011
    Location
    Savannah, GA
    MS-Off Ver
    Excel 2007
    Posts
    6

    Question Adding worksheets to a group for printing

    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
    Last edited by althean; 08-29-2011 at 04:18 PM. Reason: Added Code Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1