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

New Member
Joined
Mar 16, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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 =)?

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:

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

jsb1921

Board Regular
Joined
Aug 14, 2020
Messages
70
Office Version
  1. 2007
Platform
  1. Windows
  2. Web
in line 13 change
VBA Code:
xlsm", MASTER_STRAP_OEM_PE
to
VBA Code:
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.
 

Forum statistics

Threads
1,141,310
Messages
5,705,677
Members
421,404
Latest member
Mikecollo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top