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

[kevin67]

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

 

[John_w]

Try this macro. You don't say what the files should be renamed as, so I've used a simple incrementing count.

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]

Try this macro. You don't say what the files should be renamed as, so I've used a simple incrementing count.

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