"Save As" Macro problem

[levanduski00]

First let me begin by mentioning that I'm a complete beginner, but I very much want to learn to program macros to make my life easier at work.

My current project requires that I take four specific worksheets out of a workbook of 20 sheets and create a new workbook saved as "MMM_DD" format. For example "Oct_01". The date would be obtained using a cell refence from one of the copied worksheets.

I used the macro recorder to do most of the programming, however I'm very unclear how I can use the information from a specific cell to specify the filename. The refernce cell is a date, and I want to save the file by date.

Please let me know if you have any suggestions or if you need more information regarding my situation.

Thanks,
Roger

 

[Jack in the UK]

Hi

Always best to post up what you have ion code even if dont work or has error, so we have some thing to work on.

Then we can add to what you have makes learning easir

 

[Legacy 98055]

Hi.
Welcome...

The macro recorder is great. I use it all of the time but it is very limited and the code, even if it works for you, can usually be optimized. There are very many methods which cannot be duplicated with the recorder.

As for your current problem. Always post you code for a better example. This assumes that your date is in sheet1, cell A1 and saves the workbook accordingly. Change the path and sheet names accordingly.

<pre>
Sub CopyToNewBook()
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")).Copy
ActiveWorkbook.SaveAs "C:Documents and SettingsAdministratorDesktop" & _
Format(Sheet1.Range("A1").Value, "MMM_DD") & ".xls"
ActiveWorkbook.Close
End Sub

</pre>
Tom

 

[Yogi Anand]

Hi TsTom:

I like the code -- beautiful, compact, and does everything the OP asked!

Regards!

Yogi