On 2 Oct, 14:12, Johnnyboy5 wrote:
On 2 Oct, 13:48, Don Guillett Excel MVP
wrote:





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

Hi

thanks - however doesnt appear to work.

John

Sorry it does work - what is confusing is that when you go to file
save as - it show "documents" even thought the file does save to "my
dir" etc.

The issue is the other people who will be using this sheet wont
understand where its been save to - is there a way to change that
dialog box so it shows its saved to "mydir" etc

John