No error but result missing

[N_Reef]

Hello, may someone can help me ?...
The following code is somehow running threw without any error message and I get as well the MsgBox for the right year as the MasgBox "Done". But in the end no folder was copied to the destination.

I have the feeling it is something wrong with the part of mydate/ MeinDatum. Somehow it can not find it ?
Could it be that it can not find the list including the country file names anymore as I added more lines and therewith the list moved? If yes how can I link it correctly again?

Thank you a lot in advance. This topic is hanging here since weeks.
BR, Nadine

Sub Copy_and_Rename_Files()

    Dim sourceFolder As String, destinationYearFolder As String
    Dim copyFileNames As Variant, copyFileName As Variant
    Dim subfolderName As String
    
    Dim MeinDatum As String
    MeinDatum = Range("G4").Value
    MsgBox MeinDatum
       
    sourceFolder = "C:\Users\N\OneDrive \umbenennen der Master files IMPROVEMENT Project\Master Files\"      'CHANGE THIS
    destinationYearFolder = "C:\Users\N\Desktop\umbenennen der Master files IMPROVEMENT Project\02_Sent out Files " & MeinDatum  'CHANGE THIS"

    copyFileNames = Split("MASTER_O_Bs" & MeinDatum & ".xlsm, MASTER_O_Ds" & MeinDatum & ".xlsm, MASTER_O_H" & MeinDatum & ".xlsm, MASTER_O_P" & MeinDatum & ".xlsm, MASTER_O_T" & MeinDatum & ".xlsm", ",")
       
    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]

I try something, and if this works I will let you know. Just a Minute please =)

 

[N_Reef]

Okay did not work unfortunately.
I remember that at the beginning this code here worked but as soon as I started to do the variable for myDate it got difficult. May this can support you?

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

 

[Joe4]

Try an example of doing a FileCopy where you hard-code the two paths, and make sure it works, i.e.

FileCopy "C:\Temp\File1.xlsx", "C:\Temp\New\File1a.xlsx"

only using the real file paths and names that you are trying to create.

Once you have that working the way you want, then you know the values that you are trying to build.
So replace the hard-coded values with your calculations, and use Message Boxes to check that you have built them correctly.

So I would do it something like this:

Dim src as String
Dim dest as String

For Each copyFileName In copyFileNames
    src = ... 'put your calculation of the source file path/name here
    dest = .... 'put your calculation of the desitnation file path/name here
    MsgBox src, vbOkOnly, "Source File"
    MsgBox dest, vbOkOnly, "Destination File"
    FileCopy src, dest
Next copyFileName

If you do it correctly, the two values returned by the Message Boxes should match the hard-coded example that you were able to get working.
Keep playing around with the calculated fields until you have it working write.

 

[N_Reef]

okay, I will do so and let you know =) Thank you a lot !!