VBA to copy all but newest .txt file to another location.

[JamesRo]

Hi.
Looking for code to copy / paste all .txt files from a folder to another folder, but leave the newest where it is.

Any help appreciated, thanks.

James.

 

[John_w]

Change the source and destination folders as required.

Option Explicit

Public Sub Copy_Txt_Files_Except_Newest()

    Dim sourceFolder As String, destinationFolder As String
    Dim file As String
    Dim newestFile As String, newestDate As Date
    
    sourceFolder = "C:\path\to\folder1\"
    destinationFolder = "C:\path\to\folder2\"
    
    If Right(sourceFolder, 1) <> "\" Then sourceFolder = sourceFolder & "\"
    If Right(destinationFolder, 1) <> "\" Then destinationFolder = destinationFolder & "\"
    
    newestDate = 0
    newestFile = ""
    file = Dir(sourceFolder & "*.txt")
    While file <> vbNullString
        FileCopy sourceFolder & file, destinationFolder & file
        If FileDateTime(sourceFolder & file) > newestDate Then
            newestFile = file
            newestDate = FileDateTime(sourceFolder & file)
        End If
        file = Dir
    Wend
    
    If newestFile <> "" Then
        Kill destinationFolder & newestFile
    End If
    
End Sub

 

[JamesRo]

Oops sorry, I did not describe that too well.
This works perfectly but you copy all of the files then delete the newest from the destination it looks like.

I want to copy and remove all of the files from the source but not the newest.
TIA.

 

[John_w]

To move all .txt files except the newest:

Public Sub Move_Txt_Files_Except_Newest()

    Dim sourceFolder As String, destinationFolder As String
    Dim file As String
    Dim newestFile As String, newestDate As Date
    
    sourceFolder = "C:\path\to\folder1\"
    destinationFolder = "C:\path\to\folder2\"
    
    If Right(sourceFolder, 1) <> "\" Then sourceFolder = sourceFolder & "\"
    If Right(destinationFolder, 1) <> "\" Then destinationFolder = destinationFolder & "\"
    
    newestDate = 0
    newestFile = ""
    file = Dir(sourceFolder & "*.txt")
    While file <> vbNullString
        If FileDateTime(sourceFolder & file) > newestDate Then
            newestFile = file
            newestDate = FileDateTime(sourceFolder & file)
        End If
        Name sourceFolder & file As destinationFolder & file
        file = Dir
    Wend
    
    If newestFile <> "" Then
        Name destinationFolder & newestFile As sourceFolder & newestFile
    End If
    
End Sub

 

[JamesRo]

Thank you very much, works perfectly.