Code to print multiple sheets

[jammoca]

I'm using the following code to print only page 4 of several different sheets from the same workbooks at the press of one button ....

Sub print_multiple_sheets()
Sheets("AnnaRexia").Select
ActiveWindow.SelectedSheets.PrintOut From:=4, To:=4, Copies:=1
Sheets("MikeRaffone").Select
ActiveWindow.SelectedSheets.PrintOut From:=4, To:=4, Copies:=1
Sheets("RipTorn").Select
ActiveWindow.SelectedSheets.PrintOut From:=4, To:=4, Copies:=1
End Sub

But if these sheet names are listed in the range ... Index!D7:D9 ...I'd like to condense this code so that it refers to the sheet names in that range and prints page 4 of each of them.

How would I adjust my code ?

 

[Peter_SSs]

Try this (No error checking that all 3 cells are filled, have correct sheet names etc)

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> PrintSomeSheets()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Sheets("Index").Range("D7:D9")<br>        Sheets(c.Value).PrintOut From:=4, To:=4, Copies:=1<br>    <SPAN style="color:#00007F">Next</SPAN> c<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[jammoca]

Thanks Peter, that worked very well.

You mentioned there was no error checking in that code. I have two requests ... is there a way to adjust the code so it ....

1) ignores the fact one of the cells within the range may be blank

2) prints (the requested page 4) ONLY for those sheet names in the list that have a hash (#) symbol in the name

All the best,

Chris

 

[Peter_SSs]

Try

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> PrintSomeSheets()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Sheets("Index").Range("D7:D9")<br>        s = c.Value<br>        <SPAN style="color:#00007F">If</SPAN> Len(s) > 0 And InStr(s, "#") > 0 <SPAN style="color:#00007F">Then</SPAN><br>            Sheets(s).PrintOut From:=4, To:=4, Copies:=1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[jammoca]

Thanks so much Peter, that worked a treat.

Chris