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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,805
Messages
5,833,766
Members
430,230
Latest member
Yimmie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top