Save wookbook as now() function

[Mjack59_59]

Hello Again everyone,

Hopefully some one can help me out again. The problem is I had this code (below) working in my office, but not the one @ home. Could it be my copy paste may have lost something. The offensive code appears to be in the line formated in red. Any and all help is greatly appriciated

Application.DisplayAlerts = False
Application.EnableEvents = False
.SaveCopyAs ThisWorkbook.Path & "\backup\" & _
Left(ThisWorkbook.Name, InStr(1, LCase(ThisWorkbook.Name), ".xlt") - 1) & _
" - " & Format(Now, "m-d-yy h:mm a/p") & ".xls"
.save
Application.DisplayAlerts = True
Application.EnableEvents = True

PS Folk754, Thanks for the tip on rolling up sheets works great

[Paleo]

Hi Mjack59_59,

simply adapt the code, changing it from

Application.DisplayAlerts = False
Application.EnableEvents = False
.SaveCopyAs ThisWorkbook.Path & "\backup\" & _
Left(ThisWorkbook.Name, InStr(1, LCase(ThisWorkbook.Name), ".xlt") - 1) & _
" - " & Format(Now, "m-d-yy h:mm a/p") & ".xls"
.save
Application.DisplayAlerts = True
Application.EnableEvents = True

to

With Application
   .DisplayAlerts = False
   .EnableEvents = False
   .SaveCopyAs ThisWorkbook.Path & "\backup\" & _
      Left(ThisWorkbook.Name, InStr(1, LCase(ThisWorkbook.Name), ".xlt") - 1) & _
" - " & Format(Now, "m-d-yy h_mm a/p") & ".xls"
   .Save
   .DisplayAlerts = True
   .EnableEvents = True
End With

[mdmackillop]

Hi Mjack,
The colon in h:mm is an illegal character for a filename, try h_mm.
You also need an ActiveWork book, if it's not set earlier in your code

With ActiveWorkbook
.SaveCopyAs ThisWorkbook.Path & "\backup\" & _
Left(ThisWorkbook.Name, InStr(1, LCase(ThisWorkbook.Name), ".xlt") - 1) & _
" - " & Format(Now, "m-d-yy h_mm a/p") & ".xls"
.Save
End With

MD

[Mjack59_59]

Hello gentlemen,

Thanks for the quick replies, but still unable to resolve problem. What I'm doing is adding a client to a list then clicking a button to sort the list save to backups dir in same path and then change to different sheet. The rows after dim Str and before .save seem to be the problem.

Thanks for all the help
Mjack59

Sub sortdatabase()
' sortdatabase and move to link sheet macro
' Macro recorded 4/5/2004 by Mark Jackson
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
Range("A3").Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
With ThisWorkbook
Dim Str_TargetFile As String
.SaveCopyAs ThisWorkbook.Path & "\backup\" & _
Left(ThisWorkbook.Name, InStr(1, LCase(ThisWorkbook.Name), ".xlt") - 1) & _
" - " & Format(Now, "m-d-yy h_mm a/p") & ".xls"
.save
End With
Sheets("Link SpreadSheet").Select
Range("E1").Select
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

[mdmackillop]

I think then that your problem is here

LCase(ThisWorkbook.Name), ".xlt")

You're looking for the template extension in the workbook name. Try ".xls"
Secondly I would close and reopen the application With. While it may not cause any problems, nested with statements just seem to complicate things

With Application 
        .ScreenUpdating = False 
        .DisplayAlerts = False 
        .EnableEvents = False 
end with

[Mjack59_59]

Thanks to all

mdmackillop, that was the problem. thanks again
the help on this site is the best around

Mjack59

[mdmackillop]

Glad to help Mark.