I would like to create a macro to save a excel file to the current file location. I would like it to add to the end of the current file name data from cell(a1) that is a date format.
Here is the project. A excel document is creating a work schedule. The document is called "St Cloud.xls". It is a weekly schedule Mon-Sun. At the top is the date of the week. Cell(a1) is Mondays date"9/14" After completing the schedule I would like to save it as "St Cloud9_14.xls" or "St Cloud09142007.xls" or other date formats.
One thing if no to tough would be to edit last weeks schedule called "St Cloud20070914.xls" for week 9/21/2007 and just strip off the "20070914" and replace with the new cell(a1) value of "20070921"
I looked at many codes and like how many of them work only seems most people just want current date of save or cell value in the file name. Problems I have is.
1.Will save as but to the default save to location. I want to save to current file location
2 will grab data from cell(a1) but it is date format and tries to save as 9/14/2007.xls *not a good file name with "/" in it*
I would like to be prompted like this code:
*****UPDATE I"M GETTING THERE*****
Sub SaveSheet()
'error trap
On Error GoTo Etrap
Dim MyCell
Mydate = ActiveWorkbook.Name & Format(Range("a1").Value, "yyyymmdd")
'ask user to save
If MsgBox("Save new workbook as " & CurDir & "\" & Mydate & ".xls?", vbYesNo) = vbNo Then
Exit Sub
End If
'check value of activecell
If Mydate = "" Then
MsgBox "Please check the Cell Value", vbInformation
Exit Sub
End If
'save activeworkbook as new workbook
ActiveWorkbook.SaveAs FileName:=Mydate & ".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Etrap:
Beep
Exit Sub
End Sub
Now If I could get the ActiveWorkbook.name not to return the .xls
and also get it to strip off the last date "20070914" for week "20070921"
Here is the project. A excel document is creating a work schedule. The document is called "St Cloud.xls". It is a weekly schedule Mon-Sun. At the top is the date of the week. Cell(a1) is Mondays date"9/14" After completing the schedule I would like to save it as "St Cloud9_14.xls" or "St Cloud09142007.xls" or other date formats.
One thing if no to tough would be to edit last weeks schedule called "St Cloud20070914.xls" for week 9/21/2007 and just strip off the "20070914" and replace with the new cell(a1) value of "20070921"
I looked at many codes and like how many of them work only seems most people just want current date of save or cell value in the file name. Problems I have is.
1.Will save as but to the default save to location. I want to save to current file location
2 will grab data from cell(a1) but it is date format and tries to save as 9/14/2007.xls *not a good file name with "/" in it*
I would like to be prompted like this code:
*****UPDATE I"M GETTING THERE*****
Sub SaveSheet()
'error trap
On Error GoTo Etrap
Dim MyCell
Mydate = ActiveWorkbook.Name & Format(Range("a1").Value, "yyyymmdd")
'ask user to save
If MsgBox("Save new workbook as " & CurDir & "\" & Mydate & ".xls?", vbYesNo) = vbNo Then
Exit Sub
End If
'check value of activecell
If Mydate = "" Then
MsgBox "Please check the Cell Value", vbInformation
Exit Sub
End If
'save activeworkbook as new workbook
ActiveWorkbook.SaveAs FileName:=Mydate & ".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Etrap:
Beep
Exit Sub
End Sub
Now If I could get the ActiveWorkbook.name not to return the .xls
and also get it to strip off the last date "20070914" for week "20070921"