I'm pleased I'm on the right track
Here's a clean version, hopefully I've done everything you want (marked
with "'*"):
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
'sourced from Nick Hodge's post at
http://www.adras.com/VBA-Excel-File-SaveAs.t469-5.html
'to stop it going into an endless "before save" loop by stopping _
Excel from "seeing" the save events in this macro.
Application.EnableEvents = False 'press F9 on this line
'Creating variables for use later
Dim FilePath As String
Dim NewFileName As String
Dim CurrentFileName As String
'to stop file saving (effectively telling Excel that you pressed a
cancel button)
Cancel = True
'to check how file was being saved & save as you want it to save.
Select Case SaveAsUI
Case False
ThisWorkbook.Save
Case True
'to identify variables
FilePath = ThisWorkbook.Path
NewFileName = "Materials Manager - old.xls" '*
CurrentFileName = ThisWorkbook.Name
'to save a copy & inform user.
ActiveWorkbook.SaveCopyAs FilePath & "\" & NewFileName '*
MsgBox "A copy of """ & CurrentFileName & """" & " is now saved, in the
same directory, as """ & NewFileName & """."
End Select
'to reset Excel's ability to "see" events such as save
Application.EnableEvents = True
End Sub
To overcome the ".xls" issue I have changed the "Newfilename ="... line
to explicitly include ".xls". Also, I had wondered if you'd want to stay
in the original file & it should now happen.
Two years ago I didn't know VBA existed!
To help bring your learning time down from 12 years, see if you can
understand each line of this code by pressing F9 on the line marked in
the code (creates a breakpoint), trying to save the file each way
possible & pressing F8 to step through the code as it happens line by
line (to make it run automatically again, just press F9 on the same
line as before). Pressing F5 when you are stepping through it will make
it finish that instance of the macro automatically.
Also, just to help you optimise your copy & paste code, have a look at
the following links for some tips:
http://www.cpearson.com/excel/optimize.htm
http://excelforum.com/showthread.php?t=382670&page=2&highlight=started
(long thread but has a number of questions & solutions)
hth,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...