Code - Set Print range by sheet name

[staticbob]

Guys,

I need code that will set the print range (Pages x to x) for sheets with a certain name. These sheets will always be at the end of the workbook, there could be one, there could be 60.

If the sheet name starts with "SubCon Enquiry" then I want to include it in the print range. I could have "SubCon Enquiry 01" to "SubCon Enquiry 58", this should be my print range ?

Any starters please,
Thanks
Bob

 

[Andrew Poulsom]

A Print_Area range can only refer to one sheet. If you want to select all those sheets and print them try:

Sub Test()
    Dim Sh As Worksheet
    Dim First As Boolean
    First = True
    For Each Sh In ThisWorkbook.Worksheets
        If Sh.Name Like "SubCon Enquiry*" Then
            If First = True Then
                Sh.Select
                First = False
            Else
                Sh.Select False
            End If
        End If
    Next Sh
    ActiveWindow.SelectedSheets.PrintPreview
End Sub

I used PrintPreview instead of PrintOut to save paper.

 

[staticbob]

Thanks Andrew,

I had got this far, building a string variable that I was going to try to throw at the print dialogue pages to print range.

Is there anyway to raise the print dialogue with your solution ? Users are inevitably going to need to select a printer ?

Thanks
Bob

Sub Print_ALL_SubEnqs()
    
    'ActiveSheet.Unprotect
    Application.ScreenUpdating = False
           
'Print Out ALL Sub Enquiry Pages
Dim sht As Worksheet
Dim pagelist As String

For Each sht In ActiveWorkbook.sheets
    If Left(sht.Name, 10) = "SubCon Enq" Then
        pagelist = pagelist & ", " & sht.Index
    End If
Next sht


    Application.ScreenUpdating = True
    'ActiveSheet.Protect

End Sub

 

[Andrew Poulsom]

This will enable the user to choose a printer:

Application.Dialogs(xlDialogPrint).Show

but it will also print the selection. You can uses the BeforePrint event to cancel that and control what's printed:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Application.EnableEvents = False
    Cancel = True
'   Your code to select and print sheets
    Application.EnableEvents = True
End Sub