PDA

View Full Version : save function not working properly



niyrho
08-24-2008, 05:46 PM
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?




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

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
08-24-2008, 08:13 PM
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
08-25-2008, 12:00 AM
See if this helps...

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