You can create a BeforeClose event in which you save the file before exiting.
To do it yourself,
first, record a macro in which you do a SaveAS (specify file)
Then, in the VBA editor, choose the Thisworkbook module.
In the upper left pull-down, select Workbook
in the upper right pull-down, select BeforeClose
This will create a blank macro:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub
Next, create varables to "collect" your file name
and add the portion that you recorded.
Replace your sample file name with your "captured" name.
In Excel 2007 it could look like:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim NewName, CurName
NewName = Sheets("Sheet1").Range("G7").Value
CurName = ActiveWorkbook.FullName
If (UCase(NewName) = UCase(CurName)) Then
ActiveWorkbook.Save
Else
ActiveWorkbook.SaveAs Filename:=NewName, _
FileFormat:=xlExcel12, _
CreateBackup:=False
End If
End Sub
good luck,
Paul
----- Original Message ----
> From: enquirer <enquirer....@virgin.net>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Mon, November 15, 2010 1:34:10 PM
> Subject: $$Excel-Macros$$ AutoSaveAs
>
> Hello All
> I would like to create a macro that will save the current workbook to
> a subfolder on a mapped network drive (z:\master\CB), with a filename
> the same as the contents of cell G7 (an autogenerated number).
> I would like to automate this as much as possible, so if it can be
> done automatically when the user closes the form, that would be great,
> but if not, then I can create a button and attach the macro. I
> already have a print button, so I could incorporate the save as well
> and call it Print & Save. I would also like to create a button, that
> when clicked, opens the folder z:\master\CB, so the user can view the
> files previously saved.
> I am a self taught newbie and this is my first Excel project, so any
> help will be much appreciated. Most of what I've learnt so far, has
> been from reading posts in this group, so I thank you all for the help
> you've already provided without knowing.
> Thanks in advance for help with this new, specific requirement.
> enquirer
>
> --
>----------------------------------------------------------------------------------
>-
> 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
>
--
----------------------------------------------------------------------------------
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
