When I use the sub below, it opens the DAILY OBSERVATIONS.xltm workbook but appends a number to the end. I need it to open as the original filename because I copy sheets from the new file back into the template.
Sub SaveWithDate()
Dim fname As String
fname = ActiveWorkbook.Name
Const SubName As String = "SaveWithDate" 'The name of this routine
Const SheetName As String = "OBSERVATIONS" 'The name of the sheet that can use this sub
If ActiveSheet.Name <> SheetName Then
MsgBox "This macro can only be called from '" & SheetName & "'", vbOKOnly, SubName
Exit Sub
End If
If ActiveWorkbook.Name <> "DAILY OBSERVATIONS.xltm" Then
MsgBox "This macro can only be called from 'DAILY OBSERVATIONS.xltm. You already renamed this workbook.'", vbOKOnly, SubName
Exit Sub
End If
Dim fileName As String
Dim currentDate As String
' Get the current date in the format YYYYMMDD
currentDate = Format(Now, "yyyymmdd")
' Specify the file name with the current date
fileName = "L:DAILY OBSERVATIONS" & currentDate & " DAILY OBSERVATIONS"
' Save the workbook with the specified file name
' ThisWorkbook.SaveAs fileName
ThisWorkbook.SaveAs fileName:=fileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Workbooks.Open ("L:DAILY OBSERVATIONSDAILY OBSERVATIONS.xltm")
End Sub
3
I ended up just recording a macro and tweaking it to work how I need.
Dim currentDate As String
currentDate = Format(Now, "yyyymmdd")
Workbooks.Open fileName:="L:DAILY OBSERVATIONSDAILY OBSERVATIONS.xltm", _
Editable:=True
Windows(currentDate & " DAILY OBSERVATIONS.xlsm").Activate
End Sub