Macro to Print All Worksheets in Workbook

[mitjones]

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

[HansV]

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.

[mitjones]

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]

[HansV]

You'd need to loop through the sheets:

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

[Jezza]

[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

[HansV]

[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.

[Jezza]

Aaahhhh Silly me, this some code I had in an old workbook, I'll change it thanks

[HansV]

Mitch, you can in fact use my original macro - it skips hidden sheets automatically. No need to loop through the sheets!