Thank you Zack!


Let me elaborate a little: I save the worksheet in folders consisting of 100
P.O.'s for my numerical file list. I would also like to save them by Project
number so I do not have to search 200-300 files looking for a particular P.O.
for a particular project. realistically I would also like to save the files
by Vendor also, but first things first.

I am trying to accomplish this by not having to save the wokrsheet in 3
different folders (call me lazy). I would in, theory like the worksheet to
save itself in 3 different folders.

Am I asking the impossible?

I have entered all of the info as you stated, with the range at I5. That is
the cell that the project name is entered. I do nt see anything happening
when I save as or save the worksheet. But I don't understand VB. How and
where does the file get saved?

Thanks again for your assistance.


"Zack Barresse" wrote:

Yes, you can do this with VBA. I will be making a few assumptions here, and
will outline the assumptions in this proposed solution.

1) Press Alt + F11, to open the Visual Basic Editor (VBE)
2) Press Ctrl + R, to open the Project Explorer (PE; if not already open)
3) Select your file in left (PE)
4) Select Insert (menu) | Module
5) Copy/Paste the below code in the right (blank) pane
6) Press Alt + Q, to return to Excel
7) From the Forms toolbar, select a Command Button, create one on your sheet
where desired
8) A dialog box will come up (Assign Macro), select 'SaveTheWorkbookPlease'
and click Ok.
9) Ensure Macro security is Medium or lower (Tools | Macros | Security)
10) Save your workbook before anything else, so no work is lost.

Code to copy:


Option Explicit

Sub SaveTheWorkbookPlease()
Dim rngPO as range
Dim strName as string, strPath as string
Set rngPO = Sheets("Sheet1").Range("A1") '*
strName = ThisWorkbook.Name
strPath = Left(ThisWorkbook.Fullname, Len(ThisWorkbook.Fullname) -
Len(strName))
If Len(rngPO.Text) = 0 Then Exit Sub
ThisWorkbook.SaveAs strPath & rngPO.Text & ".xls" '**
End Sub


'* This assumes certain that your PO will be in Sheet1 in cell A1. Change
as needed.
'** Assumes you want it in the same path as the file you are saving from.
Please Note: There is no error handling in this for invalid characters so
the routine will error out. If that is not necessary you should be good to
go. It it's a possibility, I would either use Data | Validation to restrict
them from being entered in the cell. We can always test afterwards (in the
procedure) but I believe Preventative Maintenance (PM) is always best.

HTH

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)



"Spyder" wrote in message
...
I use excel to generate purchase orders.
I would like to save the spread sheets by our job number which is input
into
the same cell on each P.O..

Can I set up something that will save the P.O. by that cell location
automatically?