"Save As" Macro problem

levanduski00

New Member
Joined
Sep 18, 2002
Messages
2
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
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
 
L

Legacy 98055

Guest
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi TsTom:

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

Regards!

Yogi
 

Watch MrExcel Video

Forum statistics

Threads
1,122,331
Messages
5,595,541
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top