Problem using a loop for file directory

Why not just rename the file?
save the file in a folder
Put the path in the macro
Click on the button

Sub Test()
Dim fso, fsoFolder, fsoFile, strPath, strName
Set fso = CreateObject("Scripting.FileSystemObject")

'*********Modify Next Line *************************************
Set fsoFolder0 = fso.GetFolder("C:\Users\Mehmetcik\Desktop\New folder")

count = 0

For Each fsoFile In fsoFolder0.Files

strName = fsoFile.Name
If Not IsNumeric(Mid(strName, 21, 9)) Or Left(strName, 9) <> "File name" Then GoTo 20
Range("A1").Value = Mid(strName, 21, 9) + 5

NewName = "File name" & Year(Now) & "-" & Month(Now) & "-" & Day(Now) & "-" & Format(Mid(strName, 21, 9) * 1 + 5, "000000000") & ".xlsx"
Range("A1").Value = NewName

strPath = Left(fsoFile.Path, Len(fsoFile.Path) - Len(strName))
fso.CopyFile strPath & strName, strPath & NewName
fso.DeleteFile strPath & strName
Exit For
20  Next

End Sub

Hi,

Here's a function I use to find the latest time stamped file in a directory
You just need to pass the folder name to search through as a string. You can also remove the reference to FileSpec if all the files are the same extension.

You'll need to change the last line to

NewestFile = cLng(Right(stMostRecentFile,5))

to return the last number.

Option Explicit
Public stFileName As String
Public stMostRecentFile As String
Public dtMostRecentDate As Date

Function NewestFile(Directory, FileSpec)
' Returns the name of the most recent file in a Directory
' That matches the FileSpec (e.g., "*.xls").
' Returns an empty string if the directory does not exist or
' it contains no matching files

    If Right(Directory, 1) <> "\" Then Directory = Directory & "\"
    stFileName = Dir(Directory & FileSpec, 0)
    If stFileName <> "" Then
        stMostRecentFile = stFileName
        dtMostRecentDate = FileDateTime(Directory & stFileName)
        Do While stFileName <> ""
            If FileDateTime(Directory & stFileName) > dtMostRecentDate Then
                stMostRecentFile = stFileName
                dtMostRecentDate = FileDateTime(Directory & stFileName)
            End If
            stFileName = Dir
        Loop
    End If
    NewestFile = stMostRecentFile
End Function