VBA for copy files to a directory

[Octonet]

I have a workbook d:\private\test.xlsm with lots of tabs and data.
Each year this workbook is copied to an archive directory (d:\private\archive\"year") and cleared completely for the new year.
I made a separate workbook with a protected vba for clearing the complete workbook (the famous red button). But I want to automate the copying too.
In the red button workbook the desired year has to be entered in cell G10. The first thing the red button has to do is create a directory in the archive directory with the name of the G10 value.
After that it has to copy the test xlsm to that new directory. After that my clearing script will do the rest with the original test.xlsm.
The first part, before the clearing is my big problem.
Does anyone have a good solution ?

 

[dmt32]

Have a look at the SaveCopyAs method to see if it will do what you want

Workbook.SaveCopyAs method (Excel)

Office VBA reference topic

docs.microsoft.com

Dave

 

[Octonet]

Hello Dave,
That's no problem but...... Ik want a new directory to be created, and the name of that directory has to be the value of cell G10.
After that the workbook has to be saved to that new directory.Example : if G10 = 2020, a directory with name 2020 has to be created and the workbook has to be saved into that (G10 value) directory.

 

[dmt32]

You can use the MkDir statement

MkDir "d:\private\archive\" & Range("G10").Value

Note: The Top level folders must already exist - only the last folder specified in your range will be created.

MkDir statement (VBA)

Office VBA reference topic

docs.microsoft.com

Dave

 

[Octonet]

Thank You very much Dave, this works perfectly.
I'm just beginning to learn, and there is a lot to be learned

 

[dmt32]

Thank You very much Dave, this works perfectly.
I'm just beginning to learn, and there is a lot to be learned

most welcome - appreciate feedback

Dave

 

[Octonet]

Creating that new directory works perfect.
So I was thinking to use the same way for copying the files to that newly created directory.
But I was wrong.
I tried :
FileCopy "d:\Private\test.xlsm", "d:\privat\Archive\" & Range("C10").Value

Here I got an access error on file or path.

What stupidity did I do ?

 

[dmt32]

I tried :
FileCopy "d:\Private\test.xlsm", "d:\privat\Archive\" & Range("C10").Value

Here I got an access error on file or path.

What stupidity did I do ?

assuming the path exists, looking at your code suggests you may have a typo

FileCopy "d:\Private\test.xlsm", "d:\private\Archive\" & Range("C10").Value

Dave

 

[Octonet]

I found the stupidity
It should be :
FileCopy "d:\Privaat\test.xlsm", "d:\privaat\Archive\" & Range("C10").Value & "\" & "test.xlsm"

Thanks for the help
Problems completely solved.