none
Macro for Printing Multiple Sheets in Excel also displaying Page Breakups RRS feed

  • Question

  • Hi All,

    Is there any macro for Multiple Sheet Printing and also I should get the Display of Page Breakups and Page preview.

    The Data in the sheets is very big so there will be Page breakups, i will be manually removing the Page Breakups and reducing the font size.

    I have a simple macro for printing multiple sheets but it wont Display The Page Breakup.

    Sub Print_All()
    Dim sht
    Application.ScreenUpdating = False
    For Each sht In Sheets
    If sht.Visible And sht.Name <> "Input Sheet" Then
    With sht
    .PrintPreview
    '.PrintOut copies:=1
    End With
    End If
    Next
    Application.ScreenUpdating = True
    End Sub

    Please help me out with this

    Thanks in Advance

    -Anvesh

    Thursday, February 16, 2017 1:29 PM

Answers

  • Hi Anvesh Mudhamalle,

    if you run the code then you will find that at top print preview window will open.

    but when you close the print preview then you will find that page break preview is there.

    so code is working.

    you just need to set your code like first it only run till page break preview and after that you open print preview.

    you can ask the user by code if he want to open print preview or not.

    or you can run both code separately.

    so first you run code that display page break preview and after that you can open print preview.

    or you can create 2 buttons on the sheet, one for each task.

    because page break preview display in the sheet where as print preview open new window.

    so with your current code, the print preview window will be always on top.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 17, 2017 8:02 AM
    Moderator

All replies

  • Hi Anvesh Mudhamalle,

    you had mentioned that,"I should get the Display of Page Breakups and Page preview."

    you can use code below to see page break preview.

    Sub demo()
    Sheet1.Activate
    ActiveWindow.View = xlPageBreakPreview
    End Sub

    Output:

    to return to the normal view use the code below.

    Sub demo()
    Sheet1.Activate
    ActiveWindow.View = xlNormalView
    End Sub

    so you just need to add this code in your original code.

    Reference:

    Range.PageBreak Property (Excel)

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 17, 2017 2:52 AM
    Moderator
  • Hi Deepak,

    I tried adding that code in my macro but i cant see any page breakups display.

    this is the code

    Sub Print_All()
    Dim sht
    Application.ScreenUpdating = False
    For Each sht In Sheets
    If sht.Visible And sht.Name <> "Input Sheet" Then
    With sht
    ActiveWindow.View = xlPageBreakPreview
    .PrintPreview
    '.PrintOut copies:=1
    End With
    End If
    Next
    Application.ScreenUpdating = True
    End Sub

    Please help me out with this

    Thanks

    Anvesh

    Friday, February 17, 2017 4:55 AM
  • Hi Anvesh Mudhamalle,

    if you run the code then you will find that at top print preview window will open.

    but when you close the print preview then you will find that page break preview is there.

    so code is working.

    you just need to set your code like first it only run till page break preview and after that you open print preview.

    you can ask the user by code if he want to open print preview or not.

    or you can run both code separately.

    so first you run code that display page break preview and after that you can open print preview.

    or you can create 2 buttons on the sheet, one for each task.

    because page break preview display in the sheet where as print preview open new window.

    so with your current code, the print preview window will be always on top.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 17, 2017 8:02 AM
    Moderator