On Oct 2, 6:29*am, Johnnyboy5 wrote:
Hi

When I use the macro below *it does save the file as per cell A1 and
it does save it to the chosen drive. * But it then closes Excel.

On checking the file location it is saved there.

Any ideas what’s wrong.

Thanks

John

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
* * Dim FileSaveName

* * ChDrive "H"
* * ChDir "H:\Temp"

* * ThisFile = Range("A1").Value
* * ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub

Try it this way (saveCOPYas) to also avoid changing the default dir
\folder. Also, includes the sheet & .xls

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