Macro to create a new folder and save my workbook based cell value into the new folder + copy one more file in to this new folder.

[reinsermat]

Hello!


As usual, I would first like to apologize for my poor English..


I have a onedrive folder and an excel file in it. Additionally, there is one more .exe file in the folder (two files in one folder). I want to create a new folder every time I start working, so that I wouldn't ruin my precious template (Save As?)

I can't find a macro that creates a new folder in the same path (C:\Users\Kasutaja\OneDrive) by naming the new folder according to sheet1 ; value A2, then saves the active document in to this new folder and renames active document witch is also renamed by value at cell A2. (folder and my excel document has now both same name according value A2) . In addition, I need to copy a file called ABC.exe to this new folder without changing the file name.

After spending several hours, it must be wise to ask someone for help...

Any help please? Thank you!

Rein

 

[yky]

Try the following script.

There are few things that need to be clarified.

1. You said "save the active document". So, it is a .xlsm file?
2. What's the file format you want to save in? The following code saves the file as .xlsx.
3. Where is the ABC.exe to be copied from?

Once the code works as you desire, you can comment off the two "Application.DisplayAlerts" commands.

Sub saveas_A2()

Const mPath = "C:\temp\"
Dim filepath As String

'Application.DisplayAlerts = False

filepath = mPath & ThisWorkbook.Sheets("Sheet1").Range("A2").Value

'check if the directory already exists
If Len(Dir(filepath, vbDirectory)) = 0 Then
 
MkDir filepath

Else
'if directory already exists, exit the script
MsgBox ("The directory " & filepath & "already exists. Exiting program....")
Exit Sub

End If

'save ThisWorkbook as .xlsx. use  xlOpenXMLWorkbookMacroEnabled  if you want to save as .xlsm

ThisWorkbook.SaveAs Filename:=filepath & "\" & ThisWorkbook.Sheets("Sheet1").Range("A2").Value, FileFormat:=xlWorkbookDefault

FileCopy "C:\xxxxxx\xxxxxxxx\ABC.exe", filepath & "\" & "ABC.exe"  'use proper path for ABC.exe

'Application.DisplayAlerts = True

End Sub