Share via

Set print range for multiple sheets with a macro and then print.

Anonymous
2018-02-26T18:19:37+00:00

Hi.

I have an excel 2016 spread sheet with 43 sheets in with the data formatted in the same way for each sheet.  I would like to be able to set the print range for all sheets in the workbook to the same settings (as below) and then Print it all.

Range("$B$1:$K$60")

Page Setup is

        .Orientation = xlPortrait

        .FitToPagesWide = 1

        .FitToPagesTall = 1

I have tried the code below but it only seemed to amend the first few sheets.

Sub PrintArea()

    Dim I As Integer

    Dim ws As Worksheet

    WS_Count = ActiveWorkbook.Worksheets.Count

    For I = 1 To 43 ' Last Sheet

    Set ws = ThisWorkbook.Sheets(I)

    ws.PageSetup.PrintArea = ws.Range("$B$1:$K$60").Address

    With ws.PageSetup

        .Orientation = xlPortrait

        .FitToPagesWide = 1

        .FitToPagesTall = 1

    Application.Goto Reference:="Print_Area"

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

   End With

   ActiveSheet.Next.Select

   Next I

End Sub

Thank you very much.

<The thread has been moved to the correct category by forum moderator>

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

OssieMac 48,001 Reputation points Volunteer Moderator
2018-02-27T07:58:15+00:00

Try the following.  (Code edited since initial posting just to tidy it up a bit.)

Sub PrintArea()

    Dim i As Long

    Dim ws As Worksheet

    Dim WS_Count As Long

   'Following line changed from ActiveWorkbook to ThisWorkbook

'because ThisWorkbook used where sheet assigned to a variable.

    WS_Count = ThisWorkbook.Worksheets.Count

    For i = 1 To WS_Count

        Set ws = ThisWorkbook.Sheets(i)

        Application.Goto ws.Cells(1, 1)

        With ActiveSheet.PageSetup

            .PrintArea = "$B$1:$K$60"

            .Orientation = xlPortrait

            .Zoom = False       'Required if using FitTo in Next 2 lines

            .FitToPagesWide = 1

            .FitToPagesTall = 1

        End With

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

    Next i

End Sub

Was this answer helpful?

1 person found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-02-27T16:58:53+00:00

    Try the following.  (Code edited since initial posting just to tidy it up a bit.)

    Sub PrintArea()

        Dim i As Long

        Dim ws As Worksheet

        Dim WS_Count As Long

       

       'Following line changed from ActiveWorkbook to ThisWorkbook

    'because ThisWorkbook used where sheet assigned to a variable.

        WS_Count = ThisWorkbook.Worksheets.Count

       

        For i = 1 To WS_Count

            Set ws = ThisWorkbook.Sheets(i)

            Application.Goto ws.Cells(1, 1)

            With ActiveSheet.PageSetup

                .PrintArea = "$B$1:$K$60"

                .Orientation = xlPortrait

                .Zoom = False       'Required if using FitTo in Next 2 lines

                .FitToPagesWide = 1

                .FitToPagesTall = 1

            End With

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

        Next i

    End Sub

    Hi OssieMac

    That works perfectly. Many thanks for your help.

    Nicky

    Was this answer helpful?

    0 comments