VBA to automate printing of different selection on same sheet

mystie · Apr 8th 2017

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.

**Carim** · Apr 8th 2017

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

mystie · Apr 8th 2017

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?

**Carim** · Apr 8th 2017

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 ?

**Carim** · Apr 8th 2017

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 ...

mystie · Apr 8th 2017

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.

mystie · Apr 8th 2017

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

Quote from Carim;790049

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 ...

Display More

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.

**Carim** · Apr 8th 2017

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:

mystie · Apr 9th 2017

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

Code

Sub PrintAllSheets()


    Range("A1:W39").Select
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 35
    Range("A1:W39,A49:W53").Select
    Range("A49").Activate
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = "$A$1:$W$39,$B$49:$T$53"
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.236220472440945)
        .RightMargin = Application.InchesToPoints(0.236220472440945)
        .TopMargin = Application.InchesToPoints(0.748031496062992)
        .BottomMargin = Application.InchesToPoints(0.748031496062992)
        .HeaderMargin = Application.InchesToPoints(0.31496062992126)
        .FooterMargin = Application.InchesToPoints(0.31496062992126)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub

Display More

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.

**Carim** · Apr 9th 2017

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 ?

mystie · Apr 9th 2017

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.

**Carim** · Apr 9th 2017

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:

mystie · Apr 9th 2017

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

You mean their names?

Database, Remarks and Attendance

**Carim** · Apr 9th 2017

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 ...

**Carim** · Apr 9th 2017

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

Hello again,

Below is your code to be tested ...

Code

Sub PrintAllSheets()
' Print All Sheets excluding three sheets
Dim lr As Long
Dim sh As Worksheet


    For Each sh In ActiveWorkbook.Worksheets
      ' Exclude the three Worksheets
      If sh.Name <> "Database" And sh.Name <> "Remarks" And sh.Name <> "Attendance" Then
        ' Calculate the Individual Last Row of Each Sheet
        lr = sh.Cells(Application.Rows.Count, "W").End(xlUp).Row
        Application.PrintCommunication = True
        sh.PageSetup.PrintArea = "$A$1:$W$" & lr & ",$B$49:$T$53"
        Application.PrintCommunication = False
        With sh.PageSetup
            .PrintTitleRows = ""
            .PrintTitleColumns = ""
            .LeftHeader = ""
            .CenterHeader = ""
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = ""
            .LeftMargin = Application.InchesToPoints(0.236220472440945)
            .RightMargin = Application.InchesToPoints(0.236220472440945)
            .TopMargin = Application.InchesToPoints(0.748031496062992)
            .BottomMargin = Application.InchesToPoints(0.748031496062992)
            .HeaderMargin = Application.InchesToPoints(0.31496062992126)
            .FooterMargin = Application.InchesToPoints(0.31496062992126)
            .PrintHeadings = False
            .PrintGridlines = False
            .PrintComments = xlPrintNoComments
            .CenterHorizontally = False
            .CenterVertically = False
            .Orientation = xlPortrait
            .Draft = False
            .PaperSize = xlPaperA4
            .FirstPageNumber = xlAutomatic
            .Order = xlDownThenOver
            .BlackAndWhite = False
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .PrintErrors = xlPrintErrorsDisplayed
            .OddAndEvenPagesHeaderFooter = False
            .DifferentFirstPageHeaderFooter = False
            .ScaleWithDocHeaderFooter = True
            .AlignMarginsHeaderFooter = True
            .EvenPage.LeftHeader.Text = ""
            .EvenPage.CenterHeader.Text = ""
            .EvenPage.RightHeader.Text = ""
            .EvenPage.LeftFooter.Text = ""
            .EvenPage.CenterFooter.Text = ""
            .EvenPage.RightFooter.Text = ""
            .FirstPage.LeftHeader.Text = ""
            .FirstPage.CenterHeader.Text = ""
            .FirstPage.RightHeader.Text = ""
            .FirstPage.LeftFooter.Text = ""
            .FirstPage.CenterFooter.Text = ""
            .FirstPage.RightFooter.Text = ""
        End With
        Application.PrintCommunication = True
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
       
       End If
    Next sh
End Sub

Display More

Hope this will help ...:smile:

mystie · Apr 10th 2017

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?

**Carim** · Apr 10th 2017

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 ...???

mystie · Apr 10th 2017

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.

**Carim** · Apr 10th 2017

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 ...

Code

Sub PrintAllSheets()
     ' Print All Sheets excluding three sheets
    Dim lr As Long
    Dim sh As Worksheet
     
    For Each sh In ActiveWorkbook.Worksheets
         ' Exclude the three Worksheets
        If sh.Name <> "Database" And sh.Name <> "Remarks" And sh.Name <> "Attendance" Then
             ' Calculate the Individual Last Row of Each Sheet
            lr = sh.Cells(Application.Rows.Count, "W").End(xlUp).Row
            Application.PrintCommunication = True
            sh.PageSetup.PrintArea = "$A$1:$W$" & lr & ",$B$49:$T$53"
            Application.PrintCommunication = False
            With sh.PageSetup
                .BlackAndWhite = False
                .Zoom = False
                .FitToPagesWide = 1
                .FitToPagesTall = 1
            End With
            Application.PrintCommunication = True
            ActiveWindow.SelectedSheets.PrintOut Copies:=1
        End If
    Next sh
End Sub

Display More

mystie · Apr 10th 2017

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

Still printing on 2 pages.

VBA to automate printing of different selection on same sheet

Participate now!

Share