Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    New Lounger
    Join Date
    Mar 2013
    Posts
    7
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Need Excel print macro to exclude few worksheets from printing

    Hello group,
    I am trying to program a macro that will exclude certain worksheets when an entire excel workbook is printed. I got the following code after an internet search, and it works well for a single sheet to be excluded (let's call the worksheet 'aaa').

    Sub donít_pirnt_sheet()
    Dim sht
    Application.ScreenUpdating = False
    For Each sht In Sheets
    If sht.Visible And sht.Name <> "aaa" Then
    With sht
    .PrintOut copies:=1

    End With
    End If

    Next

    Sheets("aaa").Select
    Application.ScreenUpdating = True
    End Sub

    Now I am trying to add a couple of other worksheets (say 'bbb' and 'ccc') to the ones not to be printed, but don't know how to add those to the code. Could someone suggest the correct language? Thanks so much in advance.

    -Satnair

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Satnair,

    This code will cycle through the all the sheets in the workbook and will print all but those listed in the specified line of code.

    HTH,
    Maud

    Place in a standard module:
    Code:
    Sub don’t_pirnt_sheet()
        Dim sht
        Dim ShtName
        ShtName = Array("aaa", "bbb", "ccc") 'ADD SHEET NAMES HERE NOT TO PRINT
    For I = 1 To Worksheets.Count
        For J = 0 To UBound(ShtName)
            If Worksheets(I).Name = ShtName(J) Then Count = 1
        Next J
        If Count = 0 Then Worksheets(I).PrintOut copies:=1
        Count = 0
    Next I
    End Sub

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    Satnair (2015-05-03)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    Another way is to print worksheets that have a particular tab colour.
    Right-click on worksheet tab to change it's colour.

    Or, you could print sheets that don't have any tab colour, as in this example:
    Code:
    Sub printSheets()
    Application.ScreenUpdating = False
    For Each z In ThisWorkbook.Worksheets
    If z.Visible And z.Tab.ColorIndex = xlColorIndexNone Then
    z.PrintOut copies:=1
    End If
    Next
    
    Sheets(1).Select
    
    End Sub
    zeddy

  5. The Following User Says Thank You to zeddy For This Useful Post:

    Satnair (2015-05-03)

  6. #4
    New Lounger
    Join Date
    Mar 2013
    Posts
    7
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thanks, that method worked perfectly!

    I have a follow-up request: I'd like to be able to create a PDF of the file, with the same stipulations (selected worksheets excluded); is that possible, and what would the code be?

    Thanks again in advance.

  7. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    I adapted my code to print chosen sheets to a pdf file:
    Code:
    'NOTE:
    'Excel processes sheets in order of appearance, 
    'i.e. left-to-right tab order
    '(order as you see the tab names at the bottom).
    
    Sub printToPDF()
    
    Dim zArray() As Variant 'declares a dynamic array
    
    'worksheet names will be placed into a dynamic array for later use
    zMax = ThisWorkbook.Worksheets.Count
    ReDim zArray(1 To zMax) 'declares the array variable with the appropriate size
    
    Application.ScreenUpdating = False
    For Each z In ThisWorkbook.Worksheets
    If z.Visible And z.Tab.ColorIndex = xlColorIndexNone Then
    i = i + 1
    zArray(i) = z.Name
    End If
    Next
    
    ReDim Preserve zArray(1 To i) 'update the array with just the required sheets
    
    'DEFINE FILENAME AND SAVE LOCATION FOR PDF FILE..
    zPath = ThisWorkbook.Path & "\"
    zFile = "myfile.pdf"
    zSaveAs = zPath & zFile
    
    'EXPORT SELECTED SHEETS TO PDF FILE..
    Sheets(zArray).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=zSaveAs, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    
    Sheets(1).Select
    
    End Sub
    zeddy

  8. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    Another way to check sheetnames is to make use of the Like operator (one of my favourites)
    For example (using code in previous post):

    'check if sheetname has 3 chars..
    If z.Name Like "???" Then 'e.g "Jan","Feb",..,"Dec"
    'do this
    End If

    'check if sheetname doesn't have 4 chars..
    If z.Name Not Like "????" Then 'e.g "Apples","Bananas",..,"Pears"

    if z.Name Like "xx*" 'sheet names that begin with "xx"


    zeddy
    Last edited by zeddy; 2015-05-03 at 17:00.

  9. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hey Y'all,

    This thread got me thinking wouldn't it be handy to have a general purpose routine for selecting sheets? So I thought I'd write one.

    So you run the code and get a multi-select list box.
    MultiSelect ListBox.JPG

    When you exit you get an array that contains only the selected sheet names which can then be passed to any other routine to operate on here's the output from the test routine I wrote:
    SelectedSheets.JPG

    Or if the user doesn't make a selection.
    NoSelection.JPG

    Form Code:
    Code:
    Option Explicit
    
    '*** This UserForm requires a Public Variable to be ***
    '*** declared in the main module as follows:        ***
    '*** Public zSelectedSheets() As String             ***
    
    Private Sub UserForm_Initialize()
    
        Dim sht        As Object
        Dim iShtCntr   As Integer
        
        ReDim z(1 To ActiveWorkbook.Sheets.Count, 1 To 2)
        
        For Each sht In ActiveWorkbook.Sheets
           lBoxShtNames.AddItem sht.Name
        Next sht
    
    End Sub   'UserForm_Initialize
    
    
    Private Sub cmdExit_Click()
    
       Dim lShtCnt  As Long
       Dim lCurItem As Long
       Dim lSelCnt  As Long
       
       lSelCnt = 0   '*** If zero at end NO Items Selected! ***
       lShtCnt = lBoxShtNames.ListCount
       ReDim zSelectedSheets(lShtCnt)
       
       For lCurItem = 0 To lShtCnt - 1
          If lBoxShtNames.Selected(lCurItem) Then
            lSelCnt = lSelCnt + 1
            zSelectedSheets(lSelCnt) = lBoxShtNames.List(lCurItem)
          End If
       Next lCurItem
       
       If lSelCnt = 0 Then
         MsgBox "No Sheets were selected by the user!", _
                vbOKOnly + vbInformation, _
                "No User Selection"
       End If
       
       ReDim Preserve zSelectedSheets(lSelCnt)
       Unload Me
       
    End Sub    'cmdExit_Click
    Test Code:
    Code:
    Option Explicit
    
    Public zSelectedSheets() As String
    
    Sub TestufSelectedSheets()
    
        Dim iShtCntr   As Integer
        Dim iCntr      As Integer
        Dim zMsgString As String
        
        ufSelectSheets.Show
        
        iShtCntr = UBound(zSelectedSheets)
         
        If iShtCntr = 0 Then Exit Sub
        
        For iCntr = 1 To iShtCntr
           zMsgString = zMsgString & _
                        "Selected Sheet #" & Format(iCntr) & ": " & _
                        zSelectedSheets(iCntr) & vbCrLf
        Next iCntr
        
        MsgBox zMsgString, vbOKOnly + vbInformation, _
               "Selected Sheets:"
    
    End Sub      'TestufSelectedSheets
    Test File: VBA - Excel - Function - Select Sheets.xlsm

    Note: You'll need to Export the Form out of the test file above. Then Import it into any workbook you want to use it in. It will bring both the form & the code but NOT the test code module!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. The Following User Says Thank You to RetiredGeek For This Useful Post:

    zeddy (2015-05-04)

  11. #8
    New Lounger
    Join Date
    Mar 2013
    Posts
    7
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi Zeddy-
    Thanks so much for sharing your expertise; most of this is way over my head. I was able to successfully implement your first code and Maudibe's codes to print sheets I need; but for some reason, your PDF example did not work. Moreover, the tabs on my spreadsheets are already variously colored, which creates conflicts with this approach.

    I do have working method that creates PDF files out of a workbook where I specify which sheets to select (shown below). Problem is, some workbooks have more than 30 sheets, so I'd rather have a method where I ask it NOT to include certain sheets in the PDF file. Would you be able to tweak the code below to accomplish the task? In the example below, I would need sheets aaa, bbb, and ccc to be excluded form the PDF file.

    Satnair.


    Sub Pdf_selsheet()
    '
    ' Save selected sheets in PDF Macro
    '
    Dim X As String
    Dim BeamWidth As Integer
    BeamWidth = Range("G38").Value

    Dim FSO As Object
    Dim s(1) As String
    Dim sNewFilePath As String


    ThisWorkbook.Sheets(Array("aaa", "bbb", "ccc")).Select
    Sheets("aaa").Activate

    ' // ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\temp\test.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    ' IgnorePrintAreas:=False, OpenAfterPublish:=False

    Set FSO = CreateObject("Scripting.FileSystemObject")
    s(0) = ThisWorkbook.FullName

    If FSO.FileExists(s(0)) Then
    '//Change Excel Extension to PDF extension in FilePath
    s(1) = FSO.GetExtensionName(s(0))
    If s(1) <> "" Then
    s(1) = "." & s(1)
    sNewFilePath = Replace(s(0), s(1), ".pdf")

    '//Export to PDF with new File Path
    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=sNewFilePath, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True
    End If
    Else
    '//Error: file path not found
    MsgBox "Error: this workbook may be unsaved. Please save and try again."
    End If

    Set FSO = Nothing

    '
    End Sub

  12. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    Just to let you know, I tested my printToPDF routine as in post#5.
    I copied the code into a test file, using Excel2007 for this test.
    (it is always helpful to let us know the Excel version you are using).
    I created 6 sheets in the test file.
    I added dummy test data on each sheet to identify each sheet.
    I then changed the tab colour on two of the test sheets.
    I saved the test file into a test folder.
    Running the code (as posted) generated the single PDF file OK, with data only from each of the sheets that did NOT have a tab colour.
    The PDF file was created and saved in the same folder location as the file containing the code.
    The PDF file was named "myfile.pdf" as per the vba code posted.
    Did you get a pdf file??

    Anyway, as you have sheets that already have a tab colour, and you want these to be included in the output pdf file, we must use another method to get the required sheets for the pdf file.
    You have two choices:
    1. Have a defined list of the sheets you want for the pdf file, or
    2. Have a list of sheets to be excluded from the pdf file.

    If the excluded list is 'shorter', than perhaps that is the easiest way to do it.
    Try this code, which uses a Case statement to define 'excluded' sheets:
    Code:
    'NOTE:
    'Excel processes sheets in order of appearance,
    'i.e. left-to-right tab order
    '..so pdf file will be in same order as you
    'see the tab names at the bottom.
    'Move sheets to required order if required.
    
    Sub printToPDF()
    Dim z
    Dim zArray() As Variant 'declares a dynamic array
    
    'worksheet names will be placed into a dynamic array for later use
    zMax = ThisWorkbook.Worksheets.Count
    ReDim zArray(1 To zMax) 'first, make room for ALL sheets in array
    
    'freeze display for speedup..
    Application.ScreenUpdating = False
    
    'Loop through all worksheets..
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    For Each z In ThisWorkbook.Worksheets
        Select Case z.Name
        'List the excluded sheets here..
        Case "aaa", "dddd", "fffff", "ggg"      '<<add unwanted sheets here
        'ignore the specified sheets above
        Case Else
        'check if sheet is visible; i.e. excludes hidden sheets
        If z.Visible Then   'add sheet to printout array
        i = i + 1           'next array position
        zArray(i) = z.Name  'add sheetname to array
        End If              'end of test for visible sheet
        End Select
    Next
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    ReDim Preserve zArray(1 To i) 'update the array with just the required sheets
    
    'DEFINE FILENAME AND SAVE LOCATION FOR PDF FILE..
    '(change this to suit)..
    zPath = ThisWorkbook.Path & "\" 'use this folder for the saved pdf file
    zFile = Dir(ThisWorkbook.Name)  'excel filename without path
    zDot = InStrRev(zFile, ".")     'look backwards for file extension
    If zDot = 0 Then                'this file doesn't have a file extension..
    zPDF = zFile & ".pdf"           '..so just add one
    Else                            'otherwise..
    zPDF = Left(zFile, zDot) & "pdf"    'use same Excel name, but with .pdf
    End If                          'end of test for file extension
    
    zSaveAs = zPath & zPDF          'filename and folder for pdf file
    
    'EXPORT SELECTED SHEETS TO PDF FILE..
    Sheets(zArray).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=zSaveAs, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    
    Sheets(1).Select
    
    End Sub
    I have attached a test file with this code. This test also shows the mix of portrait and landscape modes in the output pdf file.
    Please save the file to a folder of your choice.

    RG and Maud have also shown other ways of doing this.
    Please let us know if this works.

    zeddy
    Attached Files Attached Files

  13. The Following User Says Thank You to zeddy For This Useful Post:

    Satnair (2015-05-04)

  14. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Satnair

    In my post#9 sample file, you could modify the code to allow 'hidden' sheets to be printed by removing the test for z.Visible

    Also, the pdf file can be named whatever you like by adapting the code.

    zeddy

  15. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi RG

    Nice Form!
    Useful - added it to my box of tricks.

    Try this:
    1.Click your [Select Sheets] button, choose 4 sheets, press the form [Exit] button.
    Result: message box shows the four selected sheets.
    2.Now Click your [Select Sheets] button, choose 6 sheets, click the form close red button [x] in top-right corner.
    Result: message box shows the previous four selected sheets.

    To fix this, add this to the code sheet for the Form:
    Code:
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then  'red button [x] clicked
    Unload Me       'unload the form
    End             'discontinue all further processing
    End If
    End Sub
    zeddy

  16. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2015-05-04)

  17. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    Nice catch! I often use similar code to prevent a user from exiting Excel that way but didn't think about here.

    I actually prefer:
    Code:
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    
       If CloseMode = vbFormControlMenu Then  '*** Red button [x] clicked.   ***
         Cancel = True                        '*** Force use of Exit Button! ***
       End If
       
    End Sub
    Forcing the user to use my form button(s)...probably should add a Red Cancel button though.

    Thanks again!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  18. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi RG

    If you disable the red [x] close button, then, yes, you should probably have a [Cancel] button.
    But I like the red [x] cancel method! Otherwise I would hide it on the Form!
    Your method is a neat way to disable the [x]

    Now, about your choice of Green for your exit button - do you have ancestors from the Emerald isle??

    zeddy

  19. #14
    New Lounger
    Join Date
    Mar 2013
    Posts
    7
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Hi Zeddy-
    That worked perfectly! I only had to make on change: last line, open-after-publish, changed form false to true. That solves my problem. Many Thanks again.

    -Satnair

  20. #15
    New Lounger
    Join Date
    Nov 2016
    Posts
    12
    Thanks
    1
    Thanked 0 Times in 0 Posts

    what about?

    is there a way to program a excel workbook that will only print
    Code:
    certain tabs when the number of certain tabs changes?
    
    
    Quote Originally Posted by zeddy View Post
    Hi
    Quote Originally Posted by zeddy View Post

    I adapted my code to print chosen sheets to a pdf file:
    Code:
    'NOTE:
    'Excel processes sheets in order of appearance, 
    'i.e. left-to-right tab order
    '(order as you see the tab names at the bottom).
    
    Sub printToPDF()
    
    Dim zArray() As Variant 'declares a dynamic array
    
    'worksheet names will be placed into a dynamic array for later use
    zMax = ThisWorkbook.Worksheets.Count
    ReDim zArray(1 To zMax) 'declares the array variable with the appropriate size
    
    Application.ScreenUpdating = False
    For Each z In ThisWorkbook.Worksheets
    If z.Visible And z.Tab.ColorIndex = xlColorIndexNone Then
    i = i + 1
    zArray(i) = z.Name
    End If
    Next
    
    ReDim Preserve zArray(1 To i) 'update the array with just the required sheets
    
    'DEFINE FILENAME AND SAVE LOCATION FOR PDF FILE..
    zPath = ThisWorkbook.Path & "\"
    zFile = "myfile.pdf"
    zSaveAs = zPath & zFile
    
    'EXPORT SELECTED SHEETS TO PDF FILE..
    Sheets(zArray).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=zSaveAs, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    
    Sheets(1).Select
    
    End Sub
    zeddy

Page 1 of 3 123 LastLast

Tags for this Thread

Posting Permissions

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