Copy most recent file and move to another folder

[Ananthak275]

Trying to create a VBA that copies the most recent file in folder based on name. The folder would have files in this format (seen below)..


In this example; I would want it to copy "AFILENAMENAMES_2022-10-20 - (Published On 2022-10-20 10-09-16)" and move to another folder.

AFILENAMENAMES_2022-10-20 - (Published On 2022-10-20 10-09-16)
AFILENAMENAMES_2022-10-19 - (Published On 2022-10-19 09-04-55)
AFILENAMENAMES_2022-10-18 - (Published On 2022-10-18 09-16-22)
AFILENAMENAMES_2022-10-17 - (Published On 2022-10-17 09-17-49)

 

[Trebor76]

Hi Ananthak275,

Try this:

Option Explicit
Sub Macro1()

    Dim strPathFrom As String, strPathTo As String, strFile As String, strFileToMove As String
    Dim dteFileDate As Date
   
    Application.ScreenUpdating = False
   
    'Paths to find the latest file from path 'strPathFrom' and then to copy that file to path 'strPathTo'. Obviously change to suit.
    strPathFrom = "C:\VBA Examples\Copy Latest File"
    strPathFrom = IIf(Right(strPathFrom, 1) <> "\", strPathFrom & "\", strPathFrom)
    strPathTo = "C:\VBA Examples\Copy Latest File\Move To Here\"
    strPathTo = IIf(Right(strPathTo, 1) <> "\", strPathTo & "\", strPathTo)
   
    strFile = Dir(strPathFrom & "*.xls*") 'For Excel files only

    Do While Len(strFile) > 0
        If CDate(Mid(strFile, 16, 10)) > dteFileDate Then
            strFileToMove = strFile
            dteFileDate = CDate(Mid(strFile, 16, 10))
        End If
    strFile = Dir
    Loop
   
    If Len(strFileToMove) > 0 Then
        FileCopy strPathFrom & strFileToMove, strPathTo & strFileToMove
        MsgBox strFileToMove & " has now been copied.", vbInformation
    Else
        MsgBox "The code not find a file to copy.", vbExclamation
    End If
   
    Application.ScreenUpdating = True

End Sub

Regards,

Robert