Discussion Locked

Macro that saves file w/name from a cell

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I'd like to make a macro that when run saves the current workbook to a new file in a certain location with a name that matches the current contents of a certain cell, A3 for instance. After data entry the clerk could run the macro and the file would be saved with the correct name. I've tried to record such a macro, but it saves the file with the same name even after I change that cell.

3 replies

by dr. dale

Create a new macro and insert the following lines of code:

Dim wbname As Variant

Range("A3").Select
wbname = ActiveCell.FormulaR1C1
ActiveWorkbook.SaveAs FileName:=wbname

This will save the current workbook to whatever is in cell A3. I would suggest adding some error checking to this procedure. There are some characters that you could type into this cell that would consitute an invalid filename, and thus cause the macro to fail.

by shanghai sam

I just came back to the site to answer my own question, although my solution was slightly different:
dim wbname as string
wbname=range("A3).Value
ActiveWorkbook.SaveAs Filename:="G:\Billing\" & wbname & ".xls"
End Sub

This discussion is closed to new replies.