Spreadsheet lists

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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.
 
Well, the code I supplied saves the file in the same directory as the
original file. As for your requests, they can be done. I do wonder why you
want the same file saved in 3 directories. This sounds grossly inefficient.
Maybe there's a better way we can set up your filing system? What is the
actual pupose of saving the files to 3 different folders?

Also, to add things to the filename, you can just amend the code. For
example, to add the contents of cell I5 and J5 into the filename (two
adjacent cells) then you would replace this line ...

ThisWorkbook.SaveAs strPath & strPath & rngPO.Text & ".xls" '**

ThisWorkbook.SaveAs strPath & Range("I5") & Range("J5") & ".xls" '**
 
Well, as a construction company, for archivial purposes, completed job files
must be maintained for a period of 10 years. Copies of all pertainent
documents must accompany the files for liability reasons alone, P.O.'s
included for obvious reasons. As Director of Procurement and located at
another location, I am obligated by our accounting firm to maintain numerical
files of all purchase orders. Vendor files allow my purchasing agents, who do
not have access to accounting functions on our accounting server, to have
accessability to vendor records during a specific period for research
purposes. I realize that there are more efficient methods, however we have to
make do with the resources given to us.
 
By having the file saved in these directories, I can save to disk by project
and through it into the file. I can archive to disk numerically as well as by
vendor all without having to search through hundreds of files for the one
purchase order we inevitably need to produce.
 
What about doubling up on the criteria. Instead of one or the other, how
about using both, thus by reducing your overhead significantly in not having
to save multiple copies of the same spreadsheet. (Which still sounds
utterly redundant to me.)
 
Not sure what you are getting at Zack.

Zack Barresse said:
What about doubling up on the criteria. Instead of one or the other, how
about using both, thus by reducing your overhead significantly in not having
to save multiple copies of the same spreadsheet. (Which still sounds
utterly redundant to me.)
Click to expand...
 
I'm a little unsure about what your needs are at the moment. The routine I
posted is not good enough for your situation. Is there a way we can modify
it? If there are additional requirements, what are they? What cell(s)
contain the criteria for your requirements? What is the format of the
filename you would like? What is the location you would like them saved to?
 
Back
Top