Printing Worksheets based on name only

[WindsorKnot]

Hi everyone,

I'm trying to figure this out. I need to print a workbook out (about 60 sheets 10 to 15 which shouldn't be printed out) based on whether the sheet contains the name _exhibit. I have a hunch that the workbook should contain an If statement and an array but I'm not sure how to code it properly.



This is what I have so far. To make things simpler, I only copied the first 6 sheets into the formula.

Sub Print_Exhibit()



Dim Shts

Shts = Array("Sheet1_Exhibit", "Sheet2_Exhibit", "Sheet3", "Sheet4_Exhibit", "Sheet5", "Sheet6_Exhibit")


Dim Shts_E
Shts_E = Filter(Shts, "_Exhibit", True, vbTextCompare)

Dim WS As worksheet


If Wks = "_Exhibit" then Wks.printout

Next wks


End Sub

Any input would be great!

 

[kieran]

Try this one

Sub Print_Exhibit()

Dim Shts As Worksheet
For Each Shts In ActiveWorkbook.Worksheets
    If Shts.Name Like "*_Exhibit*" Then
        wks.PrintOut
    End If
Next Shts

End Sub

 

[Justin_B]

Hi,
Try this...

Sub PrintSpecificSheets()
Dim nSheets As Integer
nSheets = ActiveWorkbook.Sheets.Count
For lSheets = 1 To nSheets
ActiveWorkbook.Sheets(lSheets).Activate
If Right(ActiveSheet.Name, 8) = "_Exhibit" Then ActiveSheet.PrintOut Copies:=1, Collate:=True
Next lSheets

End Sub

This will just loop through all the sheets, test if the name ends in "_Exhibit" (and print if true)

The down side of this method is that it sends the printjob one sheet at a time.

 

[Justin_B]

bugger!
Kierrens method is much better than mine

 

[WindsorKnot]

Thanks for the input!

I made the following Modification to Kieran's Code

I decide I was too lazy to always type in _Exhibit for new every worksheet that needed to be created.

Sub Print_Exhibit()
Dim Shts As Worksheet
For Each Shts In ActiveWorkbook.Worksheets
    If Shts.Tab.ColorIndex = 11 Then
        Shts.PrintOut
    End If
Next Shts
End Sub