Excel VBA Print to PDF Multiple sheets per page

Question

I have a question regarding Excel and VBA. I want to export multiple sheets as a PDF. My code works fine. What I want to do now is, merge multiple sheets on one page. Due to the height and width it makes sense to put two sheets together on one page. If I want to print it with my printer it works fine with the printer page layout setting. Is there a page layout code for vba to put two sheets onto one page together? Thanks in advance.

End Sub

Best regards,

 Sub SavePDF()
 Dim mySheets As Variant, sh


 mySheets = Array("Sheet 1", "Sheet 2", "Sheet 3", "Sheet 4")
 For Each sh In mySheets
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
    .PrintArea = "$A$1:$V$70"
    .Orientation = xlPortrait
    .CenterHorizontally = True
    .FitToPagesWide = 1

    End With
    Application.PrintCommunication = True

Next

Sheets(mySheets).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\Users\tstaecker\test.pdf", _
     IgnorePrintAreas:=True, OpenAfterPublish:=False
End Sub

Pᴇʜ · Accepted Answer · 2019-01-15 13:24:32Z

1

Even when you export multiple sheets into one single PDF file, every sheet will start on a new page in the exported PDF.

Otherwise you will need to copy everything into one single sheet first and export that summary sheet only. Excel cannot export 2 different sheets onto one single page.

Note that copying everything into one single summary sheet only makes sense if the column widths are the same in your sheets. Otherwise you will easily mess up.

answered Jan 15, 2019 at 13:24

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tim Stäcker Over a year ago

Thanks for the quick reply. I figured that this would possibly be the only way. Thanks though.

Tim Stäcker Over a year ago

Still hoping for a better solution.

Pᴇʜ Over a year ago

Well, don't want to take your hope, but there will not be any easy solution. Since you can define different page layouts (borders, orientation, footer, header, etc) for each sheet, how can Excel fiddle that together into one page eg if the first sheet is upright and the second is landscape that is technically already impossible to print it on one paper.

Collectives™ on Stack Overflow

Excel VBA Print to PDF Multiple sheets per page

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related