Macro to save file as text in a particular cell?

[dandetroit]

Is there a way to write a macro to save a workbook as a file name (such as a date) that is found in a particular cell in a worksheet?

In other words, I want to make a button that automatically saves the file to a given folder with the date it was completed. That date will be found in a cell on the worksheet.

 

[lenze]

Previously from the board.

Sub NameBook()

Dim MyName
MyName = ActiveCell.Text

ActiveWorkbook.SaveAs _
FileName:="C:MyFolder" & MyName, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

End Sub

or this will let you select directory

Sub NameBook2()
Dim MyName
MyName = ActiveCell.Text
Application.Dialogs(xlDialogSaveAs).Show _
MyName
End Sub

Edit: If you want a certain cell always, change ActiveCell.Text to Range("$A$1").Text
This message was edited by lenze on 2002-09-17 06:53

 

[RichardS]

lenze,

With this macro, can I place it in a looping macro? I have a macro which loops through all the sheets in a file, copies the contents to a new file, saves the file as a set name, and emails it to a user on a list. The users are complaining the filenames are not descriptive (of course, they're all the same) so I would like to save the file as the contents of cell A1. I think I get whet the macro does, but I'm DIM when it comes to VBA, so do I just DIM MYNAME once at the start, and then reuse the variable, or do I have to clear it, or re-DIM it, or what?

Richard

EDIT: No need to reply, I put the whole thing in ie I Dimmed MyName, then set the variable to the value of A1 in the current sheet just before I save it each time. Seems to work. But from a programming point of view, is this the right thing to do?

Richard
_________________

This message was edited by RichardS on 2002-09-19 00:00