'https://social.msdn.microsoft.com/Forums/en-US/9f9a1483-14ad-4de3-8893-57b5e6892fcc/create-a-macro-to-print-a-report-as-a-pdf-and-then-move-to-next-report-in-dropdown-list-to-create?forum=exceldev
Sub Main()
Dim p$, pdf$, r As Range, c As Range, dd As Range
'p = "C:\Users\ken\Research\Company Research\Company Reports Kath Test\"
p = ThisWorkbook.Path & "\t\"
Set dd = [E2]
Set dd = [E3]
Set r = Range(dd.Validation.Formula1)
'Debug.Print dd.Validation.Formula1
'Debug.Print r.Address(external:=True)
For Each c In r
dd = c 'Update lookups formulas by dropdown value
pdf = p & c & " " & Format(Date, "yyyymmdd") & ".pdf"
ActiveSheet.ExportAsFixedFormat xlTypePDF, pdf
Next c
End Sub
'http://www.excelforum.com/excel-programming-vba-macros/1121387-print-pdf-using-loop-condition-problem.html
Sub CreatePayslips()
Dim pdf As String, i As Integer
Dim r As Range, c As Range
Dim wb As Workbook, iwc As Integer
pdf = ThisWorkbook.Path & "\Payslips.pdf"
With Application
.DisplayAlerts = False
.EnableEvents = False
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
On Error GoTo EndNow
'Make scratch workbook
Set wb = Workbooks.Add 'scratch workbook
iwc = Worksheets.Count
With ThisWorkbook
Set r = .Worksheets("Payroll").Range("A3", _
.Worksheets("Payroll").Range("A3").End(xlDown))
For Each c In r
.Worksheets("Payslip").Range("E3").Value2 = c.Value2
.Worksheets("Payslip").Calculate
.Worksheets("Payslip").Copy after:=wb.Worksheets(wb.Worksheets.Count)
Next c
End With
'Remove blank worksheet(s) from scratch workbook
For i = 1 To iwc
Worksheets(1).Delete
Next i
Worksheets.Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdf _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
wb.Close False
EndNow:
With Application
.DisplayAlerts = True
.EnableEvents = True
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub