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

    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:

    Code
    Sub prnt()
    Sheets(Array(1, 3, 5, 8, 9, 10, 11, 12, 14)).PrintOut
    Sheets(1).PrintOut Copies:=2
    Sheets(2).PrintOut
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!

Register Yourself Login