Macro to save worksheet as separate file?

[aythieriot]

Hey all,

What I'm hoping to create is an easier way of our wine bar to check its current inventory against what they should have and ultimately provide a very basic printout which can be e-mailed to our central office and then be printed off over here.

The file, due to several macros already existing (i.e. add new product, update product list, update order to order history) the file is rather large and sending the whole macro enabled file twice weekly is unnecessary. Also, I would like the file sent to the main office to be saved under the name "OrderDATE", where date would be a stamp from the day of the order. As you may also know, changing the name of an Excel file with Macros can become a headache.

Therefore, I really just need a macro to copy the order to a new blank worksheet, filter out all products for which they ordering 0, and preferably save just the new worksheet as its own basic, smaller Excel file.

Thoughts?

Thanks, and by the way I'm using exclusively "Record Macro," not VBA savvy yet...

 

[Glory]

Workbooks.Add

Workbooks("Book1.xls").SaveAs("filename.xls")

ThisWorkbook.Worksheets("Sheet1").Copy After:=Worksheets("Sheet3")

 

[aythieriot]

Thanks, Glory, I'll give it a shot but going to need a run through VBA before using too well I would imagine. Any way to get the new file name from the sheet I just made? I.e. because the page is titled Orderone cell) + PasteValue of the date. Is it possible to, instead of typing the file´s name, reference the cell within the sheet with the date to use that for said filename?

Thanks again

 

[Glory]

MrVar = ThisWorkbook.Range("cell containing the name of the file")

Workbooks("Book1.xls").SaveAs(MyVar & ".xls")