Save with new sequence number VBA

[Panoos64]

Hi all, i would like to develop the below VBA code, so that when i save the file "Employees Finger" 2nd, 3rd, 4th time, e.t.c to enter a new sequence number at the end of the file. e.g. "Employees Finger1", "Employees Finger2" and so on.....

Tks in advance


ChDir "C:\Users\User\Desktop"
ActiveWorkbook.SaveAs Filename:="C:\Users\User\Desktop\Employees Finger.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

 

[John_w]

See if you can adapt https://www.mrexcel.com/forum/excel...number-file-name-post5049685.html#post5049685

 

[Panoos64]

Hi John, i tested and i think it works, but unfortunately i couldn't adapt it in my data. Many thanks for your support.

 

[John_w]

Public Sub SaveNext()
    Dim nextFileName As String
    nextFileName = GetNextFileName("C:\Users\User\Desktop\Employees Finger|n|.xlsx")
    ActiveWorkbook.SaveAs fileName:=nextFileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub


Private Function GetNextFileName(filePath As String) As String

    Dim n As Integer
    
    n = 0
    Do
        n = n + 1
        GetNextFileName = Replace(filePath, "|n|", n)
    Loop Until Dir(GetNextFileName) = vbNullString
    
End Function

 

[Panoos64]

Many thanks John. It works perfect now. Have a great day!