Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is it possible to have a button to click on to print all worksheets in a workbook? If so, can someone tell me how?

    Thanks!!

    Mitch

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The following macro will print all sheets in the activeworkbook:

    Sub PrintEmAll()
    ActiveWorkbook.PrintOut
    End Sub

    You can assign this macro to a custom toolbar button, or place a button from the Forms toolbar on a sheet and assign the above macro to it.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans, I forgot to ask... What if I only want to print "visible" worksheets and not print hidden???

    Thanks.


    [quote name='HansV' post='763453' date='04-Mar-2009 21:17']The following macro will print all sheets in the activeworkbook:

    Sub PrintEmAll()
    ActiveWorkbook.PrintOut
    End Sub

    You can assign this macro to a custom toolbar button, or place a button from the Forms toolbar on a sheet and assign the above macro to it.[/quote]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You'd need to loop through the sheets:
    Code:
    Sub PrintVisibleOnly()
      Dim wsh As Worksheet
      For Each wsh In ActiveWorkbook.Worksheets
    	If wsh.Visible = xlSheetVisible Then
    	  wsh.PrintOut
    	End If
      Next wsh
    End Sub

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='mitjones' post='763457' date='04-Mar-2009 21:27']Hans, I forgot to ask... What if I only want to print "visible" worksheets and not print hidden???

    Thanks.[/quote]

    Try this:
    [codebox]Sub VisibleSheets()

    Dim VisSht As Long
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets

    If VisSht = xlSheetVisible Then
    VisSht.PrintOut

    End If
    Next sh
    End Sub[/codebox]

    MTA: Change to original post as I had "unhidden" worksheets....this is basically the same as Hans' post, Thanks Hans
    Jerry

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Jezza' post='763461' date='04-Mar-2009 22:37'][/quote]
    Jerry, your code will temporarily unhide hidden sheets and print them. Mitch did *not* want to print hidden sheets.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Aaahhhh Silly me, this some code I had in an old workbook, I'll change it thanks
    Jerry

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Mitch, you can in fact use my original macro - it skips hidden sheets automatically. No need to loop through the sheets!

Posting Permissions

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