Sub Macro7()
Dim usr As String
Dim dr As String
Dim fl As String
Dim tmp As String
Dim nm As Long
'Get current user name
usr = Environ("UserName")
'Set directory
dr = "C:\Users\" & usr & "\Downloads"
'Get most recent file starting with "Book" and ending with "xls" in folder
fl = mostRecentFile(dr)
'Get next file number in line
tmp = Mid(fl, 5)
tmp = Left(tmp, Len(tmp) - 4)
nm = tmp + 1
'Save file with new name
Sheets("Tesst").Select
Sheets("Tesst").Copy
ChDir dr
ActiveWorkbook.SaveAs Filename:=dr & "\Book" & nm & ".xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
Function mostRecentFile(strFolderPath As String)
Dim fileSystem As Object: Set fileSystem = CreateObject("Scripting.FileSystemObject")
Dim folder As Object: Set folder = fileSystem.GetFolder(strFolderPath)
Dim file As Object
Dim strFileName As String
Dim fileDate As Date
'Loop through all files in directory
For Each file In folder.Files
'Find newest file that starts with "Book" and ends with "xls"
If (file.DateCreated > fileDate) And (Left(file.Name, 4) = "Book") And (Right(file.Name, 3) = "xls") Then
fileDate = file.DateCreated
strFileName = file.Name
End If
Next file
'Displays last created file name
mostRecentFile = strFileName
End Function