Copy several Files( a bundle) to several folders and rename those fitting to the folder

[N_Reef]

Can you please helps me? I am trying this now on my own for weeks.
You have such a great explanation here always but I could not find the fitting one for me.

I have to copy several files to all folders within a folder and renaming the beginning of the file name with the beginning of the folder name. This would help me a lot!

Example: We have a folder called MasterFiles which includes 5 Files with the structure as follows:
MASTER_PRODUCT1_YEAR
MASTER_PRODUCT2_YEAR
MASTER_PRODUCT3_YEAR
MASTER_PRODUCT4_YEAR
MASTER_PRODUCT5_YEAR.
Those include a some formulars and Makros so this should stay the same

Now I have another folder called 2021 and there in are 20 folders named with the following logic [Country_Countrycode]:
Germany_XXX
Sweden_XYZ
Spain_XCY
....
...
...
Now each of those country folders should get all 5 MASTER_files and instead of "MASTER" it should be named "Germany" for the Germany folder, "Sweden" for the Sweden folder and so on...
So it would for Germany look like
Germany_PRODUCT1_YEAR
Germany_PRODUCT2_YEAR
Germany_PRODUCT3_YEAR
Germany_PRODUCT4_YEAR
Germany_PRODUCT5_YEAR.

and the same for all the others.

 

[John_w]

Welcome to MrExcel forums.

Try this macro, changing the path to the MasterFiles source folder and the 2021 destination folder as required. I've assumed the MASTER_PRODUCT1_YEAR etc. files are .xls files, so change the copyFileNamesline if not.

Public Sub Copy_and_Rename_Files()

    Dim sourceFolder As String, destinationYearFolder As String
    Dim copyFileNames As Variant, copyFileName As Variant
    Dim subfolderName As String
       
    sourceFolder = "C:\path\to\MasterFiles\"    'CHANGE THIS
    destinationYearFolder = "C:\path\to\2021\"  'CHANGE THIS
   
    copyFileNames = Split("MASTER_PRODUCT1_YEAR.xls,MASTER_PRODUCT2_YEAR.xls,MASTER_PRODUCT3_YEAR.xls,MASTER_PRODUCT4_YEAR.xls,MASTER_PRODUCT5_YEAR.xls", ",")
       
    If Right(sourceFolder, 1) <> "\" Then sourceFolder = sourceFolder & "\"
    If Right(destinationYearFolder, 1) <> "\" Then destinationYearFolder = destinationYearFolder & "\"
   
    subfolderName = Dir(destinationYearFolder, vbDirectory)
    While subfolderName <> vbNullString
        If (GetAttr(destinationYearFolder & subfolderName) And vbDirectory) = vbDirectory Then
            If subfolderName <> "." And subfolderName <> ".." Then
                For Each copyFileName In copyFileNames
                    FileCopy sourceFolder & copyFileName, destinationYearFolder & subfolderName & "\" & Left(subfolderName, InStr(subfolderName, "_") - 1) & Mid(copyFileName, InStr(copyFileName, "_"))
                Next
            End If
        End If
        subfolderName = Dir()
    Wend

    MsgBox "Done"
   
End Sub

 

[N_Reef]

Oh my good ! It works perfectly. Feels like magic or next world wonder ^^! Thank you so much John ???!!
Yes, those are xlsm-files =].
Would it be possible to enter the File country name we entered in the File name as well in "tab 2" in cell "A2" of each single file? [Cell A2 is merged with Columns: A2&3 + B2&3+ C2&3+ D2&3]

Thank you a lot for helping me realizing and understanding. I try to study each line to learn =)).

BR, Nadine

 

[N_Reef]

I am not sure how this could happen but I now get the error 5: [invalid procedure call or argument]. Debugger marks following line within the mentioned code :
FileCopy sourceFolder & copyFileName, destinationYearFolder & subfolderName & "\" & Left(subfolderName, InStr(subfolderName, "_") - 1) & Mid(copyFileName, InStr(copyFileName, "_"))


What chenged inbetween:
I added some more modules to test other steps separately. I as well added following (marked red) to be flexible in using the years. I added the year 2025 in Cell D2 in active work sheet and a button linked to the makro in Excel.

Dim sourceFolder As String, destinationYearFolder As String
Dim copyFileNames As Variant, copyFileName As Variant
Dim subfolderName As String
Dim StartYear As String
StartYear = ActiveSheet.Range("StartYear")


sourceFolder = "C:\Users\ledern\Desktop\umbenennen der STRAP Master files IMPROVEMENT Project\Master Files\" 'CHANGE THIS
destinationYearFolder = "C:\Users\ledern\Desktop\umbenennen der STRAP Master files IMPROVEMENT Project\03_Sent out Files " & StartYear 'CHANGE THIS

.........

MsgBox "Done"

End Sub

However, Even though I delete the code completely and enter your original one again by only changing the file name as I did in the beginning, the error 5 shows up again.
Can you see what's wrong here?
Ah maybe this is important. when I click with the right mousekey on definition, following text shows up: "Identifier under cursor is not recognized".

Thank you for some enlightement ?
BR, Nadine

 

[N_Reef]

I could solve this. It s done - Thank you =)!