My code works as soon as replace the yellow marked parts in line 13 nd 14 (Master Files) with "2021". Line 11 works somehow (Folder).
If I leave it like it is now I get the error message: error when compiling: Syntax error
Can you please help me =)?
Thank you a lot!
BR, Nadine
If I leave it like it is now I get the error message: error when compiling: Syntax error
Can you please help me =)?
Rich (BB code):
1 Sub Copy_and_Rename_Files()
2
3 Dim sourceFolder As String, destinationYearFolder As String
4 Dim copyFileNames As Variant, copyFileName As Variant
5 Dim subfolderName As String
6
7 Dim MeinDatum As String
8 MeinDatum = Range("D2").Value
9
10 sourceFolder = "C:\Users\ledern\Desktop\umbenennen der STRAP Master files IMPROVEMENT Project\Master Files\" 'CHANGE THIS
11 destinationYearFolder = "C:\Users\ledern\Desktop\umbenennen der STRAP Master files IMPROVEMENT Project\02_Sent out Files " & MeinDatum 'CHANGE THIS"
12
13 copyFileNames = Split("MASTER_STRAP_OEM_Brakes_" & MeinDatum & ".xlsm, MASTER_STRAP_OEM_Doors_" & MeinDatum & ".xlsm, MASTER_STRAP_OEM_HVAC_" & MeinDatum & ".xlsm", MASTER_STRAP_OEM_PE_" & MeinDatum & ".xlsm,MASTER_STRAP_OEM_TCMS_" & MeinDatum & ".xlsm", ",")
15
16 If Right(sourceFolder, 1) <> "\" Then sourceFolder = sourceFolder & "\"
17 If Right(destinationYearFolder, 1) <> "\" Then destinationYearFolder = destinationYearFolder & "\"
18
19 subfolderName = Dir(destinationYearFolder, vbDirectory)
20 While subfolderName <> vbNullString
21 If (GetAttr(destinationYearFolder & subfolderName) And vbDirectory) = vbDirectory Then
22 If subfolderName <> "." And subfolderName <> ".." Then
23 For Each copyFileName In copyFileNames
24 Filecopy sourceFolder & copyFileName, destinationYearFolder & subfolderName & "\" & Left(subfolderName, InStr(subfolderName, "_") - 1) & Mid(copyFileName, InStr(copyFileName, "_"))
25 Next
26 End If
27 End If
28 subfolderName = Dir()
29 Wend
30
31 MsgBox "Done"
32
33 End Sub
Thank you a lot!
BR, Nadine
Last edited by a moderator: