The macro below works but... it’s a bit confusing for the end user in
that I does save the file name as A1 to the right drive / directory –
however it does show the end users it has done that.

It will also change it again if A1 is changed but its not clear to the
end user that the save has been done.

When the save as option is chosen in the file menu the save takes
place but in the Save As box it shows the previous file name etc.
Also if it’s a new sheet from a template it defaults to “Documents”
and I need it to default to the sheet in “my drive”

My approach to this problem might be all wrong.

What I want is to prevent the “end user” from messing up the original
sheet – but to be able to fill it in then save it with a name and file
number and leave the original un changed. Quite often they ignore the
“read only” use the form add the data and then save it over the
original before noticing what they have done.

I could use a FORM but the sheet is very complex (in the back ground)
and end users are OK with filling in their part of the sheet without
too much trouble – so a FORM is out.

NOTE – the end users are not very smart…

Thanks John

Thanks to Don Guillett for the macro below.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)


mydrive = "H:"
mydir = "Temp"
myname = Sheets("sheet1").Range("a1")
ms = mydrive & "\" & mydir & "\" & myname & ".xls"
ActiveWorkbook.SaveCopyAs Filename:=ms
End Sub