Efficient method to convert excel workbooks to PDF

Hi Loki,

I'm think I might have the extra links in the wrong spot. Only the first row of the loop is being generated. Here's the code I have:

Sub PDF()
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim LastRow As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
    Set ws1 = Sheets("xx")
    Set ws2 = Sheets("yy")
    Set ws3 = Sheets("zz")
    LastRow = Range("B" & ActiveSheet.Rows.Count).End(xlUp).Row
    SPath = "C:\”
    
    For iRow = 35 To LastRow
    
    Sheets("Input").Cells(15, 2).Value = Sheets("Input").Cells(iRow, 2).Value
    
    SName = Sheets("Input").Cells(16, 2).Value
    
Application.Calculation = xlCalculationManual
Application.CalculateBeforeSave = False
On Error Resume Next
        ws1.Copy
        Cells.Copy
        Range("A1").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        ws2.Copy After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
        Cells.Copy
        Range("A1").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        ws3.Copy After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
        Cells.Copy
        Range("A1").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=SPath & SName, Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        ActiveWorkbook.Close
    Next iRow
Application.Calculation = xlCalculationAutomatic
Application.CalculateBeforeSave = True
On Error GoTo 0
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Can you give me a hand placing the calculation lines in the right spots?

Thanks!

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

How about this?

Sub PDF()
    Dim LastRow As Long
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
        .CalculateBeforeSave = False
    End With

    LastRow = Range("B" & ActiveSheet.Rows.Count).End(xlUp).Row
    SPath = "C:\"
    
    For iRow = 35 To LastRow
        Sheets("Input").Cells(15, 2).Value = Sheets("Input").Cells(iRow, 2).Value
        SName = Sheets("Input").Cells(16, 2).Value
        On Error Resume Next
        Sheets(Array("xx", "yy", "zz")).Copy
        With ActiveWorkbook
            For i = 1 To 3
                .Sheets(i).UsedRange.Value = Sheets(i).UsedRange.Value
            Next
            .ExportAsFixedFormat 1, SPath & SName
            .Close True
        End With
    Next iRow
    On Error GoTo 0
    With Application
        .Calculation = xlCalculationAutomatic
        .CalculateBeforeSave = True
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub

My bad. change this line

.ExportAsFixedFormat 1, SPath & SName

to this

.ExportAsFixedFormat 0, SPath & SName & ".pdf"

As far as the filename goes I didn't change anything.