VBA Printing specific worksheets

[stevet1478]

Hello,

Does anyone know a VBA code to allow you to print specific worksheets/tabs?

Also, i'm looking for a code that will allow you to only print a certain number of pages in a certain worksheet. For Example, one worksheet contains 50 pages (As sometimes due to production demand, may require up to 50 pages to be printed to be placed onto 50 pallets, whereas other production orders may only require 3 pages to be printed.

I have a front sheet where it will workout how many pages are needed, so was hoping that i could use that information to generate how many pages are required to print?

 

[Joe4]

Welcome to the Board!

OK, let's say that you have a Summary sheet with two columns (A and B) that indicate what sheets you want to print, and how many pages of each you want to print.
So that data may look something like this:

Sheet Name	Pages
Sheet1	1
Sheet3	3
Sheet5	15
Sheet6	50

Then, we can run some VBA code to loop through those rows and print each sheet.
The code will look something like this:

Sub MyPrintMacro1()

    Dim lr As Long
    Dim r As Long
    Dim sh As String
    Dim pg As Long
    
'   Activate sheet that lists pages to print
    Sheets("Summary").Activate
    
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows, start in row 2
    For r = 2 To lr
'       Get sheet name and pages to print
        sh = Cells(r, "A")
        pg = Cells(r, "B")
'       Activate sheet to print
        Sheets(sh).Activate
'       Print
        ActiveWindow.SelectedSheets.PrintOut From:=1, to:=pg, Copies:=1, Collate _
            :=True, IgnorePrintAreas:=False
    Next r

End Sub