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
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
VBA Code:
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
Last edited by a moderator: