Automatically Saving a worksheet in it respective folder according to the selection in a combo box
Posted by TA on July 05, 2001 3:01 PM
I am designing a purchase order for my company and need a little help. I created a table of all of our vendors and used a combo list box and vlookup to display them and automatically insert the address, number, etc in the appropriate boxes so the person ordering would not have to (a neat trick a learned from your web site). Now I am trying to think of a way to have the completed PO saved in it's correct folder on the network automatically (with name of the folder is the same as the vendor).
A while back Mr. Excel, and the rest of you guys, showed me how to automatically save a worksheet as the the name in a specified cell. Now I want to save the worksheet in a specified folder according to the vendor that the user picks from the combo box. I am thinking that there is a way to use the cell link fom the drop down box for this purpose but I can't imagine how to do this.
Here is the code for changing the directory and saving the worksheet:
ChDir "G:\My Data\Purchase Orders\CDW\2001"
ActiveWorkbook.SaveAs Filename:= _
Do you know a way that I can make the folder name (CDW in this instance) variable according to the vendor select from the dropdown box??