Save excel file adding a number to file name

[Osmario]

Hi

I need a VBA code to save an excel file by addind a number to the end of file name.
For example Myfile.xlsm must be save as Myfile1.xlsm or Myfile2.xlsm or Myfile3.xlsm
and so on.

Best regards
Osmario

 

[John_w]

Try this. It saves in the active workbook's folder, but you can change this to a specific folder if necessary.

Public Sub Test()
    Dim fileName As String
    fileName = GetNextFileName(ActiveWorkbook.Path & "\MyFile|n|.xlsm")
    MsgBox "Next file name is " & fileName
    ActiveWorkbook.SaveCopyAs fileName  'or SaveAs
End Sub

Public 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<n><n>

</n></n>

 

[Peter_SSs]

Another possible option

Sub SaveAsNextNumber()
  Dim oldName As String, newName As String, sExt As String
  Dim i As Long
  
  oldName = ActiveWorkbook.FullName
  sExt = "." & Split(oldName, ".")(UBound(Split(oldName, ".")))
  oldName = Left(oldName, Len(oldName) - Len(sExt))
  i = Len(oldName)
  If Right(oldName, 1) Like "#" Then
    Do While Mid(oldName, i - 1, 1) Like "#"
      i = i - 1
    Loop
    newName = Left(oldName, i - 1) & Mid(oldName, i) + 1 & sExt
  Else
    newName = oldName & 1 & sExt
  End If
  ActiveWorkbook.SaveAs Filename:=newName
End Sub

 

[Osmario]

Thanks John_w

It works perfectly!