How do I print multiple worksheets at the same time?

[Guest]

I have added a command button into my spreadsheet which prints all sheets at
once. However, I only want to print some of the worksheets. I only know the
Visual Basic code (ActiveWorkbook.PrintOut) for the entire workbook. Can
anyone tell me the code to print only select worksheets? Thank you.

 

[Otto Moehrbach]

Two ways to do this. The first way is to select the sheets you want
printed. You do this by the selecting the first sheet you want printed,
then hold down the Ctrl key and select the rest of the sheets you want
printed. Then run this macro:

Sub PrintAll

For Each sh In ActiveWindow.SelectedSheets

'Your print command here

Next

End Sub



The other way is to use a "For" loop through all the sheets and exclude the
ones you don't want printed. That macro looks like this:

Sub AllSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name="ThisSht" Or ws.Name="ThatSht" Then GoTo NextSht
'Your print command here
NextSht:
Next ws
End Sub
HTH Otto

 

[Guest]

Can you elaborate for a VB novice? Where you write "Your Print Command
Here", I am confused. Is this what the label of the button will display? I
have added a Command Button which already says "Print Entire Workbook".
What should I state in the code in lieu of "your command here"? Thank you.

 

[Guest]

I should elaborate on my last post. This is a workbook that I have created
for users and I do not wish to require them to select worksheets prior to
printing. I want the command button to automatically print only the desired
worksheets. So far I can only get it to print the entire workbook. I have
tried the array printout method but keep encountering a runtime error.

 

[Otto Moehrbach]

I gave you two macros. In the first one the command is:
sh.Printout

In the second one it's:
ws.Printout
HTH Otto

 

[Otto Moehrbach]

If the number of sheets you want printed is large compared to the number of
sheets you don't want printed, then use the second of the two macros I gave
you. You will have to list the sheet names you don't want printed like I
did in the macro.
If you want to print only a few sheets out of a large number of sheets,
you can use the following macro. You have to list the sheets you want
printed as I did in the macro. HTH Otto
Sub PrintShts()
Dim sh As Worksheet
For Each sh In Sheets(Array("This", "That", "Other"))
sh.PrintOut
Next sh
End Sub