Hello again Mark,

Not sure of which version of xl you are using so I have included code to
create and save as xl 97-2003 plus code that I have commented out for xl2007
macro enabled workbook.

From your earlier post I assumed that N2 contained a date and therefore I
have included code to format that date in an acceptable format for a file
name because slashes cannot be used in filenames.

You might want to think about changing code so the date format is "yyyy-mm-dd"
because then your files will then sort in the correct order.

If the user answers No to the msgbox and the file has been changed since the
last save then the system will return the usual warning message to the user
as to whether they want to save the file befor close. I think this is the
best way to go because if the user wants to opt out without save they can but
if they want to save as the original filename they have that option also.


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim strPath As String
Dim strFilename As String
Dim userResponse As Variant

'Edit "Sheet1" to match the sheet where N2 is.
'Format date so that it does not have slashes _
because slashes cannot be used in a filenames _
and assign filename to string variable.
strFilename = "Week Comm " & _
Format(Sheets("Sheet1").Range("N2"), _
"dd-mm-yyyy")

userResponse = MsgBox("Do you want to save as " _
& strFilename, _
vbYesNo + vbDefaultButton2 + vbQuestion, _
"File Save")

If userResponse = vbYes Then
'Can use a string to create alternative _
path in lieu of ThisWorkbook.Path
strPath = ThisWorkbook.Path

'Concatenate path and filename and save file _
'Use following code for xl versions 97 - 2003
strFilename = strPath & "\" & strFilename & ".xls"
ThisWorkbook.SaveAs Filename:=strFilename, _
FileFormat:=xlNormal, _
CreateBackup:=False

'Use following code for xl2007 macro enabled.
'strFilename = strPath & "\" & strFilename & ".xlsm"
'ThisWorkbook.SaveAs Filename:= strFilename , _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
CreateBackup:=False
Else
'Else only reuired if alternative code
'required if user answers no
End If

End Sub

--
Regards,

OssieMac