[SOLVED] Print to PDF but with different page sizes for different sheets

Print to PDF but with different page sizes for different sheets

I have multiple Excel sheets that I want to export to PDF. The contents on each sheet is a different size and shape. In the PDF I want to retain the font size, layout etc, so I want the pages of the PDF to be different sizes so that each shows the original scale of the sheet contents. My macro current uses the code below but it fixes the page size. How can I get each pdf page to be the size of the print area?

 Worksheets("Sheet1").PageSetup.PrintArea = "A1:J39"
 Worksheets("Sheet2").PageSetup.PrintArea = "A1:I23"

 Sheets(Array("Sheet 1", "Sheet 2", "Sheet 3", "Sheet 4")).Select
 ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Filename, _
    openafterpublish:=False, IncludeDocProperties:=True, IgnorePrintAreas:=False

Solution

Sadly, custom page sizes are not something that users can define. However, there are many page sizes available through the XlPaperSize Enumeration.

If you are using the same ranges every time, and those ranges have a fixed width and height, perhaps one of the enumeration's sizes will work for you.