Macro to save file as text in a particular cell?

dandetroit

New Member
Joined
Sep 16, 2002
Messages
15
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Well-known Member
Joined
Feb 16, 2002
Messages
761
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? :confused:

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
_________________
constrMining.gif

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

Forum statistics

Threads
1,144,765
Messages
5,726,173
Members
422,659
Latest member
RGP268

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