Move all files except recent one to another folder.

Ananthak275

Board Regular
Joined
Aug 22, 2020
Messages
56
Office Version
  1. 2013
Platform
  1. Windows
  2. MacOS
Hi,

I'm trying to create a VBA where it moves all the files except the most recent one to a new folder (example; //documents/data/))

The file names are consistent format (Example: FileName-2021-07-28). as the only thing that changes is the date. For example, within a folder it can contain these files;
FileName-2021-07-28.xlsx
FileName-2021-07-27.xlsx
FileName-2021-07-26.xlsx


move files both 27 and 26.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,128
Try this macro, changing the folder paths as needed.
VBA Code:
Public Sub Move_Files_Except_Most_Recent()

    Dim fromFolder As String, toFolder As String, filename As String
    Dim filesArray As Object
    Dim i As Long
    
    fromFolder = "C:\folder\path\"
    toFolder = "C:\another\folder\"
    
    If Right(fromFolder, 1) <> "\" Then fromFolder = fromFolder & "\"
    If Right(toFolder, 1) <> "\" Then toFolder = toFolder & "\"
    
    Set filesArray = CreateObject("System.Collections.ArrayList")
    
    filename = Dir(fromFolder & "*.xlsx")
    Do While filename <> vbNullString
        i = InStrRev(filename, "-", InStrRev(filename, "-", InStrRev(filename, "-") - 1) - 1)
        filesArray.Add Mid(filename, i + 1) & "|" & filename
        filename = Dir()
    Loop
    
    filesArray.Sort
    
    For i = 0 To filesArray.Count - 2
        Name fromFolder & Split(filesArray(i), "|")(1) As toFolder & Split(filesArray(i), "|")(1)
    Next
    
End Sub
 
Solution

Ananthak275

Board Regular
Joined
Aug 22, 2020
Messages
56
Office Version
  1. 2013
Platform
  1. Windows
  2. MacOS
Try this macro, changing the folder paths as needed.
VBA Code:
Public Sub Move_Files_Except_Most_Recent()

    Dim fromFolder As String, toFolder As String, filename As String
    Dim filesArray As Object
    Dim i As Long
   
    fromFolder = "C:\folder\path\"
    toFolder = "C:\another\folder\"
   
    If Right(fromFolder, 1) <> "\" Then fromFolder = fromFolder & "\"
    If Right(toFolder, 1) <> "\" Then toFolder = toFolder & "\"
   
    Set filesArray = CreateObject("System.Collections.ArrayList")
   
    filename = Dir(fromFolder & "*.xlsx")
    Do While filename <> vbNullString
        i = InStrRev(filename, "-", InStrRev(filename, "-", InStrRev(filename, "-") - 1) - 1)
        filesArray.Add Mid(filename, i + 1) & "|" & filename
        filename = Dir()
    Loop
   
    filesArray.Sort
   
    For i = 0 To filesArray.Count - 2
        Name fromFolder & Split(filesArray(i), "|")(1) As toFolder & Split(filesArray(i), "|")(1)
    Next
   
End Sub
Perfect, thank you!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,115
Messages
5,768,204
Members
425,459
Latest member
Danniey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top