Autosaving
 

To cut a long story short...

I've got a spreadsheet that will be ran automatically.
The spreadsheet opens, does some calculations and then is
supposed to save with the file name being the date of the
first day of that month. This is just a monthly report.

I'm receiving a problem however trying to close and save
the spreadsheet from within the code. Below is the code
I'm using when closing the spreadsheet.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Not done Then
MsgBox filename 'just verifying the name is being passed
through, which it is.

ActiveWorkbook.SaveAs ("C:\MonthlyReport\" & filename)
End If

End Sub

Now this code works should I close the spreadsheet
myself, manually, within Excel. However, should I use the
line...

ThisWorkbook.Close

The spreadsheet prompts me with the

"Would you like to save changes to Monthly Report.xls"

And on clicking Yes/No... does not appear to run:

ActiveWorkbook.SaveAs ("C:\MonthlyReport\" & filename)

any more, the report for that month is not saved.

Anyone have any ideas why this might be occuring? Thanks.