Export Multiple Worksheets to single PDF but retain existing print area/page layout of all exported worksheets

38 Views Asked by At

I have a drop down list in cell B20 of a worksheet called "TIN Scorecard Page 1".

I have additional worksheets in the same workbook called 'TIN Scorecard Page 2' and 'TIN Scorecard Page 3' and the contents of each worksheet updates based on the selection in cell B20 of TIN Scorecard Page 1

Each worksheet already has a dynamically preset print area and page layout/setup.

I have a macro that loops through the drop down list in cell B20 of 'TIN Scorecard Page 1', and exports each worksheet (worksheets 1-3 to a single PDF File).

The problem is that my code is applying the print layout of worksheet 1 to worksheets 2 and 3.

How do I retain the existing print areas/layout of Worksheets 1, 2 and 3 in the final exported PDF?

Thanks for your help

Public Sub Create_PDFs()

    Dim destinationFolder As String
    Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
    
    destinationFolder = ThisWorkbook.Path     'Same folder as workbook containing this macro
    'destinationFolder = "C:\path\to\folder\"  'Or specific folder
    
    If Right(destinationFolder, 1) <> "\" Then destinationFolder = destinationFolder & "\"
    
    'Cell containing data validation in-cell dropdown
    
    Set dataValidationCell = Worksheets("TIN Scorecard Page 1").Range("B20")
    
    'Source of data validation list
    
    Set dataValidationListSource = Evaluate(dataValidationCell.Validation.Formula1)
    
      'Create PDF for each data validation value
    For Each dvValueCell In dataValidationListSource
        dataValidationCell.Value = dvValueCell.Value
      With dataValidationCell.Worksheet.Range("Print_Area")
        
    'Merge PDFs to single file
    
        Sheets(Array("TIN Scorecard Page 1", "TIN Scorecard Page 2", "TIN Scorecard Page 3")).Select
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=destinationFolder & dvValueCell.Value & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With
    Next

End Sub
0

There are 0 best solutions below