Save multiple PDF files from excel sheets with successive numbering in the name string

[hassanleo1987]

Hi,

I am creating multiple PDFs from a multi sheet workbook.
Previously, I used pre defined file names like this, which was working OK.

File1 = ActiveWorkbook.Path & "\Part - 1.pdf"
File2 = ActiveWorkbook.Path & "\Part - 2.pdf"
File3 = ActiveWorkbook.Path & "\Part - 3.pdf"

But now I have added some conditions due to which some time one file is skipped making the series irregular.

I am looking for a dynamic file name were the string part "Part - " remains constant but the number changes successively based on last exported PDF file.
For example, if Part - 1.pdf has been created but Part - 2 is being skipped due to existing logic, the Part - 3 should be exported with the successive name as Part - 2.


I tried to do a loop for the filename but its not working and all parts are being printed as same file name, replacing the previous one.

n = 1
    Do
        n = n + 1
        FNm = ActiveWorkbook.Path & "\Part - " & n & ".pdf"
    Loop Until Dir(FNm) = ""

For saving excelsheets as PDF I am using this:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FNm, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, from:=1, to:=1, OpenAfterPublish:=False

where FNm should be coming from the Do loop and change with every successive PDF export.

I would really appreciate some help here!

 

[hassanleo1987]

Additional Info, All PDF export files are being saved in the same directory as the excel file.
So the last file created can also be check in the same directory.