VBA to automate printing of different selection on same sheet

  • Hello to all.


    I have a number of sheets, and for each sheet I need to print the following to an A4 size sheet of paper:


    Range A1:T42. (However, this is not fixed. The number of rows may vary.)
    Range B49:T53.


    Both ranges will need to be printed on the same sheet of paper.


    How can I automate this using a command button with macro so it prints all the worksheets in one go?


    Thanks in advance.

  • Re: VBA to automate printing of different selection on same sheet


    Hello mystie,


    For a dynamic ending row ... you can use

    Code
    last = ActiveSheet.Cells(Application.Rows.Count, "T").End(xlUp).Row
    and use Range ("A1:T" & last )


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: VBA to automate printing of different selection on same sheet


    Won't that return row 53 as it's the last row that contains data?

  • Re: VBA to automate printing of different selection on same sheet


    Quote from mystie;790047

    Won't that return row 53 as it's the last row that contains data?


    Yes ... you are right ...


    Do you mean it's not what you are looking for ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: VBA to automate printing of different selection on same sheet


    Mystie,


    Why don't you turn on your macro recorder ... and go through your process once ...


    As a result, you will get a "VBA translation" of your actions ...


    Then, your code can always be refined and improved ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: VBA to automate printing of different selection on same sheet


    No. Above row 49 I have a set of data which starts at row 1 and ends anywhere before row 49 on different sheets.


    However range B49:T53 is static on all the sheets.

  • Re: VBA to automate printing of different selection on same sheet




    I've done that. But the code is so long and I'm not an expert in VBA. So I'm having a hard time understanding what needs changing.


    And when you record a macro, it records everything as a sequence.


    And like I've said the number of rows differ from sheet to sheet. I know I can use a loop "For each sheet in workbook" to consider every sheet.


    But how to take into consideration the variable number of rows and how to fit everything into an A4 sheet is giving me trouble.

  • Re: VBA to automate printing of different selection on same sheet


    My recommendation would be to record your macro only for one worksheet ... and post it in your next message ...


    The Loop can be added at a later stage ...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: VBA to automate printing of different selection on same sheet



    This is the code for sheet 1. The Print area is A1:W39, $B$49:$T$53
    On sheet 2, the print area will be A1:W41, $B$49:$T$53


    Like this the number of rows will vary from sheet to sheet.

  • Re: VBA to automate printing of different selection on same sheet


    Thanks a lot for your code ...


    My guess is your code performs what you expect for Sheet1 ... and you would like to extend it to other sheets ...


    So, is it actually ALL sheets ... or all sheets with some exceptions ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: VBA to automate printing of different selection on same sheet


    All sheets with some exceptions.


    But like I said the number of rows in the first range varies across the sheets.

  • Re: VBA to automate printing of different selection on same sheet


    Quote from mystie;790106

    All sheets with some exceptions ...


    Do you mind to mention precisely the sheets you need to exclude from the macro ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: VBA to automate printing of different selection on same sheet


    Quote from mystie;790114

    You mean their names?


    Database, Remarks and Attendance


    In order to add a Loop For Each Sheet ....


    there is the need to exclude your three worksheets Database, Remarks, Attendance ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: VBA to automate printing of different selection on same sheet


    Hello again,


    Below is your code to be tested ...



    Hope this will help ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: VBA to automate printing of different selection on same sheet


    Thanks Carim.. It does print for all sheets. However, the ranges are still printing on 2 pages. The "Fit to 1 page" isn't working. Any particular reason for this?

  • Re: VBA to automate printing of different selection on same sheet


    You are welcome ...


    Your macro is identical to what you posted ... and I have not tested it ...


    Was your macro working fine before you posted it ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: VBA to automate printing of different selection on same sheet


    Actually I didn't test it. :oops:


    I did it now and it's printing on 2 pages as well.

  • Re: VBA to automate printing of different selection on same sheet


    Quote from mystie;790213

    Actually I didn't test it. :oops:


    I did it now and it's printing on 2 pages as well.


    ... I will not get back to our initial exchanges ... and the macro recorder ...:wink:


    Let me take a look into your macro ...


    Coud you test the following code ...


    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

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