save function not working properly

[niyrho]

I'm pretty new to VBA. Trying to get a file to automaticly chose a file name and save the workbook when you try to exit it. Its not finding the name or letting me exit. Can anyone tell me whats wrong with my code?




[VBA] Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo Exits
Cancel = True
Application.EnableEvents = False
With ActiveSheet
ActiveWorkbook.SaveCopyAs "C:\TimeSheets\" & Format(Worksheets(3).Range("A12"), " yy-mm-dd") & Worksheets(3).Range("H10") & ".xls"
End With
Exits:
Application.EnableEvents = True
End Sub[/VBA]

Aussiebear: When adding sections of code, please use the VBA button to wrap your code. It makes the code so much easier to read.

[akanchu]

Hi,

Try using ActiveWorkbook.SaveAs ......
But this should be used for the first time to save.

For subsequent saves you will have to use SAVE

Hope this helps.

[ilyaskazi]

See if this helps...

[VBA]Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo Exits
Application.EnableEvents = False
With ActiveWorkbook
.SaveCopyAs "C:\TimeSheets\" & Format(.Worksheets(3).Range("A12"), "YY-MM-DD") & .Worksheets(3).Range("H10") & ".xls"
End With
Exits:
Application.EnableEvents = True
End Sub [/VBA]