Create PDF from different ranges in multiple Excel

Question

I need to export several Excel sheets into one PDF file. I am currently using the following:

Sub CreatePDF()

    Sheets("ReportPage1").Select
    Range("Print_Area").Select
    Sheets("ReportPage2").Select
    Range("Print_Area").Select
    Sheets("ReportPage3").Select
    Range("Print_Area").Select

        ThisWorkbook.Sheets(Array("ReportPage1", "ReportPage2", "ReportPage3")).Select
        Selection.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            FileName:="C:\temp\temp.pdf", _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True

    Sheets("ReportPage1").Select
    Range("A1").Select

End Sub

The problem is that all print areas are different. The PDF that is generated is using the range address of print area from ReportPage1 for all the pages.

I tried in Excel itself, outside of VBA, and when I have selected, for example, ReportPage1!A1:E30 and ReportPage2!A1:F70, when I select both sheets it will change the selected ranges to ReportPage1!A1:E30 and ReportPage2!A1:E30.

Any ideas how to solve this?

Any help will be much appreciated.

Answer 1

Ok, I solved it. If I don't select any ranges on the specific pages it will automatically get the Print_Area range of each sheet.

Sub CreatePDF()

    ThisWorkbook.Sheets(Array("ReportPage1", "ReportPage2", "ReportPage3")).Select

    Sheets("ReportPage1").Activate

    ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            FileName:="C:\temp\temp.pdf", _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True

    Sheets("ReportPage1").Select
    Range("A1").Select

End Sub