Hello OssieMac

I am getting the following error.

Run Time error 9
Subscript out of range

at line: strFilename = "Galashiels Resources WC " &
Format(Sheets("Sheet1").Range("N2"), "dd-mmm-yyyy")

Do you have any ideas.

Regards

Mark

"OssieMac" wrote:

Hello again Mark,

I am assuming that your "out of range error" was when trying to save with
your required path in the code. The code you posted with ThisWorkbook.Path
should work. If something does not work I suggest that you post the code
didn't work and advise which line of code failed with the error message. I
have modified the code to the path you requested.

Until you get it working properly I suggest that you leave the space and
underscore at the end of lines which is a line break in an otherwise single
line of code. This allows the code to be posted on the forum and copied into
the VBA editor without getting errors due to the code breaking at the wrong
place.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim strPath As String
Dim strFilename As String
Dim userResponse As Variant
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
strPath = "c:\User\me\Desktop"

strFilename = strPath & "\" & _
strFilename & ".xls"

ThisWorkbook.SaveAs _
Filename:=strFilename, _
FileFormat:=xlNormal, _
CreateBackup:=False
End If
End Sub


--
Regards,

OssieMac