Macro help - appending a date to a file name when saving

I'm trying to create a macro that saves a file in a specific format (macro-enabled workbook), specific title (Inventory <date in yyyy-mm-dd format>), and in a specific place (the same folder the Inventory template is in rather than defaulting to My Documents). Basically all the user should have to do is click a Save File button I'd add on the worksheet and everything else would be done (to take out the possibility of human error). To easily be able to document inventory history, I'd like to be able to have a folder that just has files like

Inventory 2012-03-03
Inventory 2012-03-10
Inventory 2012-03-17
etc.

If it's easier, the word "inventory" could just be taken out and each sheet could just be saved as the date. The yyyy-mm-dd format is preferred, but if it's simpler to do as a different format it's not a big deal. I have a basic knowledge of macros in that I can record them, attach them to things, and do some VERY basic editing of them in VB. How can I set up a macro so that when the user clicks a button the document is automatically saved with the correct file name in the correct folder?

Is there a way to just append the current date to the file name? The folder will be the same one all the time, and if it happens to change that's something I could just manually change in the code. I'd tried using this:

Please Login or Register  to view this content.

and I think I found the issue. When I hit Debug on the error and hovered over "TodaysDate" in the Active.Workbook line, it showed the date as "3/10/2012" and files can't be saved with a /.

When I changed the cell type that TodaysDate looks at to Text and input the date as "3-10-2012" then it saved properly.

This brings up one more question - is there a way to do this automatically? I know that ctrl + ; sticks the current date in a cell, but it's formatted as x/x/xx. And changing the cell's formatting to show with dashes instead of slashes doesn't work (as that's how I had it the first time you posted code for me to try). Edit: Nevermind; I can just use an Auto_Run macro to insert the current date in a hidden cell, then use DAY, MONTH, and YEAR functions to separate it.

If there's no easy way to do it, then this will still work great.

Also, eventually I plan to make a kind of cover worksheet that has all of the buttons so the user only has to go to one page. In VB, can I reference cells just as in formulas like below?

Please Login or Register  to view this content.