Code - Set Print range by sheet name

staticbob

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
A Print_Area range can only refer to one sheet. If you want to select all those sheets and print them try:

Code:
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

Well-known Member
Joined
Oct 7, 2003
Messages
1,079
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

Code:
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This will enable the user to choose a printer:

Code:
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:

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

Watch MrExcel Video

Forum statistics

Threads
1,118,455
Messages
5,572,216
Members
412,448
Latest member
ManuW
Top