Print and export sheets by common tab name

[Dunk4Divin]

Hi

I have a workbook which currently has 60+ sheets. Some of these are generated by copying the format from a "master" form and creating a series of work instructions for which the Tabs are named "Gauge Instruction (X)". The suffix is the normal excel generated number. What I need to be able to do is select all sheets with the words "Gauge Instruction" without the suffix and then:

1 Print hard copies
2 Export to a new workbook
3 Export as PDFs.

Any assistance will be greatly appreciated.

Thanks

 

[sijpie]

Sub ExportAll()
    Dim wsWS As Worksheet
    Dim wbWB As Workbook, wbThis As Workbook
    Dim sIncl As String, sFind As String
    
    sIncl = "Gauge Instruction" ' Name (excl order number) of all sheets to process
    
    
    Set wbThis = ThisWorkbook
    
    sFind = sIncl & "*"         ' add search asterix
    
    For Each wsWS In ThisWorkbook.Sheets
        If wsWS.Name Like sFind Then
            ' sheet found
            ' make a copy to new workbook
            wsWS.Copy
            Set wbWB = ActiveWorkbook
            wbWB.SaveAs Filename:=wsWS.Name
            wbWB.Close
            ' print as pdf
            wsWS.ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:="C:\TestFolder\temp.pdf", _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=True
            ' print to printer
            wsWS.PrintOut
        End If
    Next wsWS
End Sub

 

[Dunk4Divin]

Thanks very much