Automatic saving

P

please help me

I would like to include a button on a worksheet that when clicked will save
the worksheet as "purchase order" plus a number that is entered in a cell on
the worksheet.

How would I go about this?
 
J

Jason Morin

1. Copy the code below:

Dim sPath As String
sPath = "C:\Documents and Settings\jmorin\Desktop\"
ActiveWorkbook.SaveAs Filename:=sPath & _
"Purchase Order " & Range("A1").Value & ".xls"

2. Go to View > Toolbars and select "Control Toolbox"
3. Click on "Command Button" and draw a button on your
worksheet.
4. Right-click and go to "View Code".
5. Paste in the code from step 1.
6. Press ALT+Q to go back to Excel.
7. Press the "Exit Design Mode" the Control Toolbox
toolbar.

The code is set up to look for a PO# in A1. Change the
path to suit your needs.

HTH
Jason
Atlanta, GA
 
G

Guest

hi,
Put this in the button's code module
Sub commandbutton1_click()
Dim n As Range
Set n = Range("A1")
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"H:\CodeStuff\PO" & n & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:= _
"", ReadOnlyRecommended:=False _
, CreateBackup:=False
Workbooks.Open Filename:="H:\CodeStuff\POtest.xls"
ActiveWindow.ActivateNext
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub
 
Top