I noticed that the email was off-line instead of to the group.. so I included it here. ----------------------------------------------------------------------------------------------
My solution seems to work if the file hasn't been named, so I got more elaborate: -------------------------------------------------------------------------------- Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim FileSaveName Cancel = True ChDrive "H" ChDir "H:\data" FileSaveName = Application.GetSaveAsFilename( _ fileFilter:="Excel Files (*.xls*), *.xls") If FileSaveName <> False Then MsgBox "Save as " & FileSaveName Application.EnableEvents = False ActiveWorkbook.SaveAs FileSaveName End If Application.EnableEvents = True End Sub ------------------------------------------------------- Hi Paul Thanks - it works - but only if its on "C" drive or sub folders etc. It doesn't like it if I use and "S" drive or a memory stick drive "F" etc any suggestions John On 30 September 2010 18:43, Paul Schreiner <schreiner_p...@att.net> wrote: > right-click on the sheet "tab" name. > select "View Code" > > This will open the VBA editor > the panel on the right side SHOULD list the sheet in the workbook > as well as a "sheet" called "ThisWorkBook" > (if you don't see this panel, hit Ctrl-R) > > double-click "ThisWorkbook" > > At the top of the large "editor" window, you'll see two pull-down lists. > > The left one says: (General) > change it to "Workbook" > > By default it creates a Workbook_Open() event. > > you can delete this one. > > in the right-hand pull-down, select BeforeSave > > it will create: > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) > End Sub > > add the Chdir line with the appropriate folder name so it looks like: > > Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) > Chdir "C:\temp" > End Sub > > > save the template and you should be on your way! > > Paul > ----- Original Message ---- >> From: Johnnyboy5 <intermediatec...@gmail.com> >> To: Paul Schreiner <schreiner_p...@att.net> >> Sent: Thu, September 30, 2010 1:18:09 PM >> Subject: Re: $$Excel-Macros$$ macro to save it to a particular drive / file >>location. >> >> Thanks, >> >> I understand the idea but dont know how to write the macro to do it. >> >> many thanks >> >> John >> >> On 30 Sep, 13:19, Paul Schreiner <schreiner_p...@att.net> wrote: >> > If you're allowing the users to define the name of the file they're saving >> > (instead of having the program do it) >> > then, in someplace like the Workbook_BeforeSave event, >> > use: >> > >> > Chdir "C:\temp" >> > >> > (or whatever you want the "default" folder to be) >> > >> > Paul >> > >> > >> > >> > >> > -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts