Save wookbook as now() function

Hello Again everyone,

Hopefully some one can help me out again. The problem is I had this code (below):banghead: 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

Code:

---

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:thumb

Hi Mjack59_59,

simply adapt the code, changing it from

Quote:

---

Originally Posted by Mjack59_59

Code:

---

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

Code:

---

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

---

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

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

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

Code:

---

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

---

I think then that your problem is here

Code:

---

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

Code:

---

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

---

Thanks to all

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

Mjack59

Glad to help Mark.