VBA - transfer specific excel files from several folders to a main folders

kevin67

Board Regular
Joined
Feb 22, 2020
Messages
52
Office Version
  1. 2016
Platform
  1. Windows
Hello VBA expert,

I am looking assistance to the following issue that i am having. I have several folders example c:\test1 ,c:\test2. In each folder i have several excel files and the files name are saved as current date example 2020-09-08.xlsx. I need to move all the files for a specific month to one specific folder c:\masterfolder. Example In an input box i insert 2020-09 and all files for the month of september from folders test1 and test2 being transfer to masterfolder and be rename otherwise it will overwrite the files when it is copies to the masterfolder since we will get same file name from folders test1 and test2

Hoping that someone can assist will be greatful


Thanks for your cooperation
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,539
Try this macro. You don't say what the files should be renamed as, so I've used a simple incrementing count.

VBA Code:
Public Sub Move_Files()

    Dim YearMonthInput As String
    Dim destinationFolder As String, sourceFolder As Variant
    Dim file As String, fileNum As Long
    
    destinationFolder = "C:\MasterFolder\"
    
    YearMonthInput = InputBox("Enter year and month (YYYY-MM) of files to move")
    If YearMonthInput = "" Then Exit Sub
    
    If Right(destinationFolder, 1) <> "\" Then destinationFolder = destinationFolder & "\"
    
    fileNum = 0
    
    For Each sourceFolder In Array("C:\Test1\", "C:\Test2\")
    
        If Right(sourceFolder, 1) <> "\" Then sourceFolder = sourceFolder & "\"
    
        file = Dir(sourceFolder & YearMonthInput & "*.xlsx")
        While file <> vbNullString
            fileNum = fileNum + 1
            Name sourceFolder & file As destinationFolder & fileNum & " " & file
            file = Dir
        Wend
        
    Next
    
    MsgBox "Done"
    
End Sub
 

kevin67

Board Regular
Joined
Feb 22, 2020
Messages
52
Office Version
  1. 2016
Platform
  1. Windows
Try this macro. You don't say what the files should be renamed as, so I've used a simple incrementing count.

VBA Code:
Public Sub Move_Files()

    Dim YearMonthInput As String
    Dim destinationFolder As String, sourceFolder As Variant
    Dim file As String, fileNum As Long
   
    destinationFolder = "C:\MasterFolder\"
   
    YearMonthInput = InputBox("Enter year and month (YYYY-MM) of files to move")
    If YearMonthInput = "" Then Exit Sub
   
    If Right(destinationFolder, 1) <> "\" Then destinationFolder = destinationFolder & "\"
   
    fileNum = 0
   
    For Each sourceFolder In Array("C:\Test1\", "C:\Test2\")
   
        If Right(sourceFolder, 1) <> "\" Then sourceFolder = sourceFolder & "\"I modify 
   
        file = Dir(sourceFolder & YearMonthInput & "*.xlsx")
        While file <> vbNullString
            fileNum = fileNum + 1
            Name sourceFolder & file As destinationFolder & fileNum & " " & file
            file = Dir
        Wend
       
    Next
   
    MsgBox "Done"
   
End Sub
Thank you so much John. You have make my life easier. I modify the code so that it makes a copy in the masterfile folder instead transfer it. Once again thank you
 

Watch MrExcel Video

Forum statistics

Threads
1,119,009
Messages
5,575,532
Members
412,673
Latest member
KD23
Top