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