Excel VBA Export Multiple sheets to PDF

Question

I have been trying to export a named range on multiple worksheets to a single pdf. Initially, it seemed to work fine, then I noticed the the selected range would change when loading the various sheets into an array.

It is my understanding by using Selection.ExportAsFixedFormat this would export the selected cells only. Therefore, I have a macro to loop through the required sheets, select the various ranges need then create an array for all the sheets to allow exporting to a single pdf.

Dim wb As Workbook
Dim srcSht As Worksheet
Dim toPrnt As String

Set wb = ThisWorkbook
Set srcSht = wb.Sheets("print_array")

Dim myArr1() As Variant
    myArr1 = srcSht.Range("myPrintArray")

Dim i As Long
Dim j As String
    For i = LBound(myArr1, 1) To UBound(myArr1, 1)
            j = myArr1(i, 1)

    wb.Sheets(j).Activate
        wb.ActiveSheet.Range("CCB_" & Left(j, 5) & "_Print").Select

    Next i

wb.Sheets(Array("CAT01 - Request for Resource", _
                            "CAT02 - Resource Allocation", _
                            "CAT03 - Product Data Sources", _
                            "CAT04 - Target & Control Cells", _
                            "CAT05 - Matching And Deduping", _
                            "CAT06 - Exclusions", _
                            "CAT07 - Data from other teams", _
                            "CAT08 - Outputs", _
                            "CAT09 - Special Instructions", _
                            "CAT10 - Brief Meeting Sign Off" _
                            )).Select

Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF_FileAndPath, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True

When stepping through the code, everything goes to plan right up to creating the sheets array, at this point the selected ranges change.

I have also tried using PageSetup, but the result is the same.

With ActiveSheet.PageSetup
    .Orientation = xlLandscape
    .PaperSize = xlPaperA4
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .PrintArea = Range("CCB_" & Left(j, 5) & "_Print").Address
End With

After reviewing a few similar post in this forum, I am still at a loss.

Can anyone shed light on why the selected range changes when creating the array or, have any other suggestions that may help?

Many thanks

Answer 1

I was having the same issue. I came across another, easier solution. No need for temp files.

After you .select your array, instead of Selection.ExportAsFixedFormat, use ActiveSheet.ExportAsFixedFormat. Because your sheets are made active with your initial .select, ActiveSheet then selects all desired sheets to export.

Your code should look like this:

Dim wb As Workbook
Dim srcSht As Worksheet
Dim toPrnt As String

Set wb = ThisWorkbook
Set srcSht = wb.Sheets("print_array")

Dim myArr1() As Variant
    myArr1 = srcSht.Range("myPrintArray")

Dim i As Long
Dim j As String
    For i = LBound(myArr1, 1) To UBound(myArr1, 1)
            j = myArr1(i, 1)

    wb.Sheets(j).Activate
        wb.ActiveSheet.Range("CCB_" & Left(j, 5) & "_Print").Select

    Next i

wb.Sheets(Array("CAT01 - Request for Resource", _
                            "CAT02 - Resource Allocation", _
                            "CAT03 - Product Data Sources", _
                            "CAT04 - Target & Control Cells", _
                            "CAT05 - Matching And Deduping", _
                            "CAT06 - Exclusions", _
                            "CAT07 - Data from other teams", _
                            "CAT08 - Outputs", _
                            "CAT09 - Special Instructions", _
                            "CAT10 - Brief Meeting Sign Off" _
                            )).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF_FileAndPath, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True

Answer 2

I managed to solve my problem by copying the selected ranges to a temporary file then exporting from there. The complete solution looks like this...

Dim wb As Workbook
Dim srcSht As Worksheet
Dim tempFile As String

Set wb = ThisWorkbook
Set srcSht = wb.Sheets("print_array")

Dim myArr1() As Variant
    myArr1 = srcSht.Range("myPrintArray")

Dim i As Long
Dim j As String
    For i = LBound(myArr1, 1) To UBound(myArr1, 1)
            j = myArr1(i, 1)

    wb.Sheets(j).Activate

    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .PaperSize = xlPaperA4
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintArea = Range("CCB_" & Left(j, 5) & "_Print").Address
    End With

        Next i

    wb.Sheets(Array("CAT01 - Request for Resource", _
                                "CAT02 - Resource Allocation", _
                                "CAT03 - Product Data Sources", _
                                "CAT04 - Target & Control Cells", _
                                "CAT05 - Matching And Deduping", _
                                "CAT06 - Exclusions", _
                                "CAT07 - Data from other teams", _
                                "CAT08 - Outputs", _
                                "CAT09 - Special Instructions", _
                                "CAT10 - Brief Meeting Sign Off" _
                                )).copy

                With ActiveWorkbook
                    .Save

                    tempFile = .FullName

                    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF_FileAndPath, _
                        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                        :=False, OpenAfterPublish:=True

                    .Close

                End With

                Kill tempFile

   End If

I hope this helps someone in the future.

Thanks.