Code:
'this assumes the files in the folder are assigned
'with 3 numbers as last sequence of the naming convention
'example..file001,file002,file003 or whatever name
'but always a 3 digit number

'will work for files ranging from 001 to 999


Option Explicit

'this is used to sort and array by alpha

Sub iSort(iArray As Variant)
     
     Dim Loop1 As Long
     Dim Loop2 As Long
     Dim Temp As String
  
     For Loop1 = UBound(iArray) To LBound(iArray) Step -1
       For Loop2 = LBound(iArray) + 1 To Loop1

         If iArray(Loop2 - 1) > iArray(Loop2) Then
           Temp = iArray(Loop2 - 1)
           iArray(Loop2 - 1) = iArray(Loop2)
           iArray(Loop2) = Temp
         End If
       Next Loop2
     Next Loop1
   End Sub

Private Sub Command1_Click()

    Dim stFile As String
    Dim sDir As String
    Dim i As Integer
    Dim myArr()
    Dim myLen As Integer
'the folder I used for testing this was C:\abc
'you change it to your folder

    stFile = Dir$("c:\abc\*.*")
    
'loop through the folder and get the names of all files
    Do While stFile <> ""
       myLen = Len(stFile) 'used in left string function

'redim and preserve the array as it's inside a loop
       ReDim Preserve myArr(i)
       
'take the .ext from the filename
      myArr(i) = Left(stFile, myLen - 4)
      
'increment for the array index
      i = i + 1
      
'next file in directory
      stFile = Dir
      
    Loop
    
'we now have the filenames so we sort them to put
'the last number on the bottom
    Call iSort(myArr)
    
'now we need to get the last 3 digits from the filename
'we need this because the filename is a string and you
'cannot add to a string so we extract them and edit them
    
    Dim NewFile As String
'newfile = the last item after we sort
    NewFile = myArr(UBound(myArr))
'now we take the last 3 digits from it's name
    NewFile = Right(NewFile, 3)
'we change the string to an integer so we can add
    NewFile = CInt(NewFile)
'we add 1 to the number we extracted
    NewFile = NewFile + 1
'we fromat it to show 3 characters at all time
    NewFile = Format(NewFile, "000")
'we display the number you will need to use
    MsgBox "My new number is " & NewFile

End Sub