VBA: code to select printer and paper size

    I need to select all the sheets in a workbook and print out all of them at the same time. I can get the macro to work in one book, but when I switch to another book, the defalut size to print is A4 and not what I set it at in the original macro (which is 11 x 17) "Need a little help"

    Hello Matrixman,


    If you could post a little more information it would make it helpful in order to help.


    Such as...is this app. working on a network? or posting the code your currently using could help.


    Pass this on and we can assist.


    Best Regards,


    Phil

    Hey Phil, thanks for the reply. Sorry I didn't make myself very clear. What I have is several sheets in a workbook. Each sheet's print paper size needs to be changed from A4 to 11 x 17. I'm using this code to accomplish this change.
    Dim ws As Worksheet
    For Each ws In Worksheets
    ws.PageSetup.PaperSize = xlPaper11x17
    But, (there's always a but) when I go to print out these sheet (all grouped together), the printer does not recgonize the print settings for all the sheets, just the first one. Since there are several sheets, I want to print them together at one time and duplex them. Since the print settings are only applied to the first sheet, end up with 2 times the paper. I am printing to a network copier. Hope this is clearer. "Need a little help"

    Okay let's try a couple of things.


    If you are using something like...


    ActiveWorkbook.PrintOut Copies:=1, Collate:=True


    as youre syntax to print the entire workbook then try...


    Dim ws As Worksheet
    For Each ws In Worksheets
    ws.PageSetup.PaperSize = xlPaper11x17
    ws.PrintOut Copies:=1, Collate:=True
    Next ws



    This will print each page individually. Now if your wanting multiple copies to print then the following should do the trick...


    Dim ws As Worksheet
    Dim i as Interger
    For i = 1 to 10 '''Whatever is the total number of copies to print'''
    For Each ws In Worksheets
    ws.PageSetup.PaperSize = xlPaper11x17
    ws.PrintOut Copies:=1, Collate:=True
    Next ws
    Next i


    Have not tested but seems pretty sound.


    Good luck let me know how it works.


    Best Regards,


    Phil

    Phil, sorry for the long delay in reply. I was so busy print out the 700 page catalog that I just haven't made it back to say THANKS! Really appreciate your help.


    Why not just like this


    For Each ws In Worksheets
    ws.PageSetup.PaperSize = xlPaper11x17
    ws.PrintOut Copies:=10, Collate:=True
    Next ws

    If you walked away smiling-then for you the price was right

    Hi kundepuu,




    This method would print 10 copies of each sheet at a time before going onto the next sheet in the workbook. Someone would then have to collate them together.


    From the original post I thought the goal was to collate the pages as they are in the workbook.


    I may have miss read...


    Kindest Regards,


    Phil

    Right you are Phil. I needed to collate the pages as they are in the workbook. Glad to see the interest in my post. The next step is to find a way to have the print set up to be applied to all the grouped pages in each workbook at once. Printing out the same paper size works, Thanks to you guys. The problem is, when I send these grouped work sheets to the copier to be printed duplex, the formats only seem to apply to the first sheet. I still haven't got the thing to print duplex. "need a little help"

    Sorry for the late reply...I've been traveling and will not be back in my office until Monday.


    I'll try to take a look soon.


    Phil

    Hi guys, any ideas on how to select the printer before printing tho? Cheers,


    victor

    ___________________
    "Time is the best teacher; unfortunately it kills all of its students..."

Participate now!

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

Register Yourself Login