Code to append to an existing PDF or rename

[Obsidian]

Hi, I am using the VBA code below to export to PDF, and saving as the sheet name. Unfortunately, when I come across the same sheet name (from a different workbook) it overwrites the existing PDF.

I would ideally like to append to the existing PDF instead of overwriting. If that is not possible, then even renaming the 2nd, 3rd, etc with a 1, 2 3 at the end (or something similar) would be great. Thanks for the help.

Sub ExportToPDFs()
Dim ws As Worksheet


For Each ws In Worksheets
ws.Select
nm = ws.Name


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Users\X\Documents\" & nm & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False


Next ws


End Sub

 

[V_Malkoti]

Try this:

Sub ExportToPDFs()
    Dim ws As Worksheet
    Dim i As Integer
    Dim dirPath As String, fileName As String, ext As String
    Dim x As Variant
    
    dirPath = "C:\Users\X\Documents\"
    ext = ".pdf"
    
    For Each ws In Worksheets
        ws.Select
        nm = ws.Name
        
        fileName = nm
        i = 0
        
        Do
            x = Dir(dirPath & fileName & ext)
            If x = "" Then Exit Do
            i = i + 1
            fileName = nm & "_" & i
        Loop While True
        
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            fileName:=dirPath & fileName & ext, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next ws

    
End Sub

 

[Obsidian]

That works great, thanks!