Vba to save as with path and file name

[Xalova]

What i ultimatly want to achive is that i save the active workbook under the name of the date today in the path i choose. I wrote a little code for that, that partly helped me.

the code is as follows:

Sub save()
Dim location As String
location = ThisWorkbook.Path

ChDir location & "\History"
ThisFile = Format(Date, "dd-mm-yyyy") & ".xlsm"
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub

now this works. better than i hoped, actually. one little thing though. I cant override the current file, if this file already exists. When i try to save again with this macro i somehow get the error that the path could not be found. Is there any workaround for that?

Also, and that would be the cherry on top, it would be perfect if it didnt open the newly saved file, but let the original file open. What i mean is that if i save the file named "Historymaker" with this macro, it would automatically open the file "04-12-2021.xlsm", which i want to avoid. <--- is no must tho.

 

[KOKOSEK]

What do you mean that you can't overwrite already existed file?You get message about it, right?
You can avoid it by:

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=ThisFile
Application.DisplayAlerts = True

with this you will overwrite existed file without message.
Running macro from file named for ex. 04-12-2021.xlsm gives you error about path because there is no \History\History path. I hope you know what I mean.

 

[Xalova]

What do you mean that you can't overwrite already existed file?You get message about it, right?
You can avoid it by:

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=ThisFile
Application.DisplayAlerts = True

with this you will overwrite existed file without message.
Running macro from file named for ex. 04-12-2021.xlsm gives you error about path because there is no \History\History path. I hope you know what I mean.

i figured out why it doesnt work.

ChDir location & "\History"

thats why. When i save the file i have the new file open instead of the original. And since the file is now in the folder "history" it wont find that folder again.

(im sorry if you cant understand me :/ )

how do i avoid that though?

 

[Zot]

I think this is what you meant. This will help

Save As Keeping Original Open

How an Excel VBA Macro can save copies of the workbook while keeping the workbook open

www.mrexcel.com

 

[Xalova]

I think this is what you meant. This will help

Save As Keeping Original Open

How an Excel VBA Macro can save copies of the workbook while keeping the workbook open

www.mrexcel.com

yup. that perfectly worked, thank you