Save as pdf instead of print preview VBA

[saamrat]

Hi,

The following code creates print preview for each row. But someone please help me to save as pdf instead of print preview.
There are list of employees and details in data sheet. Payslip generates and show print preview for each employee when you run this code. But instead of print, I need to save payslip for each employee in their name separately

>>> use code - tags <<<

Sub SavePaySlips()
    Dim rCl As Range
    Dim rRng As Range
    With Sheet1
        Set rRng = .Range(.Cells(6, 1), .Cells(.Rows.Count, 1).End(xlUp))
        For Each rCl In rRng
            With Sheet16
                .Cells(6, 2).Value = rCl.Value
                .PrintPreview
            End With
        Next rCl

    End With
End Sub

 

[Keetoowah]

Hi saamrat
try this code, adapt the path as needed

Sub SavePaySlips2()
'https://chandoo.org/forum/threads/save-as-pdf-instead-of-print-preview-vba.50058/

    Dim rCl As Range, rRng As Range
    Dim MyPath As String
    
    MyPath = "C:\Users\Keetoowah\Forum\" '<===== Change to your path
    
    Application.ScreenUpdating = False
    
    With Sheet1
        Set rRng = .Range(.Cells(6, 1), .Cells(.Rows.Count, 1).End(xlUp))
        For Each rCl In rRng
            With Sheet16
                .Cells(6, 2).Value = rCl.Value
               '  .PrintPreview
               .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                MyPath & Cells(6, 2).Value & ".pdf", Quality _
                :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
            End With
        Next rCl
    End With
    
    Application.ScreenUpdating = True
End Sub