Code to print selected sheets

  • Hi,
    I have been searching for similar questions to my pain but most answers are for more advance request.
    I have a workbook with 20 sheets. the sheets are from sheets 1-20.
    I would like to print the following:
    Sheet1, Sheet3, Sheet5,Sheet8,Sheet9,Sheet10,Sheet11,Sheet12 and Sheet14. I also want to repeat a few sheets. So in the end I really want this:

    Sheet1, Sheet3, Sheet5,Sheet8,Sheet9,Sheet10,Sheet11,Sheet12 and Sheet14,Sheet1,Sheet1,Sheet2.

    I found a neat VBA code

    Sub prnt()
    Sheets(Array(1, 3, 5, 8, 9, 10, 11, 12, 14, 1, 1, 2)).PrintOut
    End Sub

    But it does not reprint the repeated sheets like 1 & 2.
    Does anyone know what I am doing wrong?

    Thanks so much

  • Re: Code to print selected sheets

    Simple but perhaps unsatisfying solution:

    Sub prnt()
        Sheets(Array(1, 3, 5, 8, 9, 10, 11, 12, 14)).PrintOut
        Sheets(1).PrintOut Copies:=2
    End Sub

    The alternative would be to take the array as a parameter and iterate through it printing each sheet individually. I am not aware of a way to have a collection return multiple instances of the same object (which is what you are asking effectively by passing an array to the Sheets collection with duplicate identifiers).

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!