copyfilenames: file name includes the year "2021" which is a variable and should get changed if I change cell D2 in active worksheet

[N_Reef]

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 =)?

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

 

[jsb1921]

in line 13 change

xlsm", MASTER_STRAP_OEM_PE

to

xlsm, MASTER_STRAP_OEM_PE

basically remove the double quote between xlsm and ,Master.
If you still have a problem, easy way to debug is to put the command in a string variable str1="Filecopy " & sourcefolder ... etc
and debug.print str1 as the next line.
in the view menu select immediate window and see what is the value of str1.
copy and paste it in a command prompt window (black color dos window) and see what error you are getting.
it is a good idea to put filenames within quote if the filename has spaces
Hope this helps.