Save With Date

[VinceF]

Greetings..
I've used this code in another workbook for golf and I'm trying to use the code for a bowling workbook. For reason(s) unknown to me I simply cannot get it to work and I've tried everything my limited skills allow...help please.

I created a Active X Control Box and tried to put the code in there but....it's just not working.

Sub SaveWithTodaysDate()
ActiveWorkbook.SaveAs ("C:\Bowling\2024-25\" & ThisWorkbook.Sheets("Scores").Range("Ar3").Value & ".xlsm")
End Sub

The worksheet name is CAM BOWLING and it's located C:\Bowling I'm trying to get it to save a copy with todays date in C:\Bowling\2024-25\
Sheet name is "Scores" and Cell "AR3" is a formula showing today's date. I do have VBA code to "RESET" the sheet...see below..

Thank You
VinceF
Office 2016

Code that Resets the sheet

Private Sub CommandButton1_Click()
Dim warning
warning = MsgBox(Range("A1").Value & " THIS WILL RESET THE ENTIRE SHEET. Select OK to continue or select CANCEL to continue without resetting", vbOKCancel, " THIS WILL RESET THE ENTIRE SHEET")
If warning = vbCancel Then Exit Sub
On Error Resume Next
Sheets("Scores").Range("C5:W33").SpecialCells(xlCellTypeConstants).ClearContents
Sheets("Scores").Range("AG5:AI33").SpecialCells(xlCellTypeConstants).ClearContents
MsgBox "THE FORM HAS BEEN RESET."
End Sub

 

[Logit]

What error/s are you receiving when running the macro ?

 

[Joe4]

Sheet name is "Scores" and Cell "AR3" is a formula showing today's date.

Note that if your date has slashes in it, that will cause errors, as slashes are not legal characters to be used in file names. So you will need to re-format your date to use something other than slashes. You can do that right in your code, so as not to have to touch your sheet, i.e.

ActiveWorkbook.SaveAs ("C:\Bowling\2024-25\" & Format(ThisWorkbook.Sheets("Scores").Range("Ar3").Value,"yyyy-mm-dd") & ".xlsm")

See here for more help and details on the VBA Format function: Format function (Visual Basic for Applications)

 

[VinceF]

Thanks Logit/Joe4 for taking the time to help...

Logit, I get runtime error 1004...cannot access file...C:\Bowling\2024-25\

Joe4 I implemented your code and it saves it in the correct folder however the file that it saved doesn't keep the results of that night. I believe it's because the code got tagged on to the bottom of the existing code that resets the sheet....does that make sense?

 

[VinceF]

Joe4...Upon further review...it did save the results...
I very much appreciate the help..
Marked as solved...!
VinceF

 

[Joe4]

You are welcome.
Glad I was able to help!

Yes, you need to be careful when creating file names to save, as there are a bunch of characters which are not allowed to be used in file names.
Here is a list here: Characters to Avoid in Filenames and Directories | Michigan Tech

 

[VinceF]

You are welcome.
Glad I was able to help!

Yes, you need to be careful when creating file names to save, as there are a bunch of characters which are not allowed to be used in file names.
Here is a list here: Characters to Avoid in Filenames and Directories | Michiga

 

[VinceF]

Thanks Joe, I'll be sure to read and learn more...!