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

 

[rollis13]

Just a quick look at the macro since there is no other information on folder paths and names:
"C:\Users\N\OneDrive \umbenennen Is the space after OneDrive correct ?
Project\02_Sent out Files " & Is the space after Files correct ?

 

[N_Reef]

t the macro since there is no other information on folder paths and na

Ah sorry, the path I copied with path copy so it should be absolutely fine. For here I just removed manually some information out of the path name as these were not meant for public. *

 

[Joe4]

Try adding some Message Boxes into the code before the FileCopy part to verify you are creating valid file path/names, i.e.
amend this:

For Each copyFileName In copyFileNames
                    Filecopy sourceFolder & copyFileName, destinationYearFolder & subfolderName & "\" & Left(subfolderName, InStr(subfolderName, "_") - 1) & Mid(copyFileName, InStr(copyFileName, "_"))
                Next

to this:

For Each copyFileName In copyFileNames
    MsgBox sourceFolder & copyFileName, vbOkOnly, "Source File"
    MsgBox destinationYearFolder & subfolderName & "\" & Left(subfolderName, InStr(subfolderName, "_") - 1) & Mid(copyFileName, InStr(copyFileName, "_")), vbOkOnly, "Destination File"
    Filecopy sourceFolder & copyFileName, destinationYearFolder & subfolderName & "\" & Left(subfolderName, InStr(subfolderName, "_") - 1) & Mid(copyFileName, InStr(copyFileName, "_"))
Next

If that does not reveal the problem to you, please post back here with what each of those Message Boxes returns.

 

[N_Reef]

MsgBox sourceFolder & copyFileName, vbOkOnly, "Source File" MsgBox destinationYearFolder & subfolderName & "\" & Left(subfolderName, InStr(subfolderName, "_") - 1) & Mid(copyFileName, InStr(copyFileName, "_")), vbOkOnly, "Destination File"

I entered the two lines like you wrote. There is nothing new happening. I still get the MsgBox with the year 2023 and then the "Done" MsgBox.

 

[N_Reef]

I entered the two lines like you wrote. There is nothing new happening. I still get the MsgBox with the year 2023 and then the "Done" MsgBox.

I tried it with entering the source & Final destination of the master files and with leaving the text 100% how you sent it

 

[Joe4]

If you entered the code like I showed you, you should get two message boxes, one for the Source Path and File Name you are building, and one for the Destination Source Path and File Name you are building.
What do those two Message Boxes return?
Please post the entire result they return.

If you are not getting any, then either your code is erroring out before you get there, or there are no values in "copyFileNames" to loop through.

 

[N_Reef]

Try adding some Message Boxes into the code before the FileCopy part to verify you are creating valid file path/names, i.e.
amend this:

For Each copyFileName In copyFileNames
                    Filecopy sourceFolder & copyFileName, destinationYearFolder & subfolderName & "\" & Left(subfolderName, InStr(subfolderName, "_") - 1) & Mid(copyFileName, InStr(copyFileName, "_"))
                Next

to this:

For Each copyFileName In copyFileNames
    MsgBox sourceFolder & copyFileName, vbOkOnly, "Source File"
    MsgBox destinationYearFolder & subfolderName & "\" & Left(subfolderName, InStr(subfolderName, "_") - 1) & Mid(copyFileName, InStr(copyFileName, "_")), vbOkOnly, "Destination File"
    Filecopy sourceFolder & copyFileName, destinationYearFolder & subfolderName & "\" & Left(subfolderName, InStr(subfolderName, "_") - 1) & Mid(copyFileName, InStr(copyFileName, "_"))
Next

If that does not reveal the problem to you, please post back here with what each of those Message Boxes returns.

OKay I changed a little thing in the path and now I get a MsgBox showing my path twice and then the next box includes following (ScreenShot attached)

 

[Joe4]

On the error in the first one, if you click the "Debug" button, which line of code does it show?

If your second message box shows two file paths, that is an issue, as it should only be showing one.
So you have some editing to do to fix it. You can use the MsgBox to make sure it is showing what you need.

 

[N_Reef]

On the error in the first one, if you click the "Debug" button, which line of code does it show?

If your second message box shows two file paths, that is an issue, as it should only be showing one.
So you have some editing to do to fix it. You can use the MsgBox to make sure it is showing what you need.

First of all, thank you for your help so far!! We got already one step further.

To your question: The second one of the two you gave me is lightened.
Oh okay,.. so I have to fix things here. But I don't understand how or what. ?