Save As Macro

[iperlovsky]

I am running the following sub routine, but I would like the new file name to
incorporate a specific cell value so that the file will always be saved in
the most current folder.

Sub SaveAs()
ActiveWorkbook.SaveAs Filename:="N:\FOLDER1\MyFile.xls"
End Sub

For example, in the modified code below I would like folders {yyyy} and
{mm-yyyy} to reference cells A2 and A3, respectively, in a specific worksheet
in the active workbook.

ActiveWorkbook.SaveAs Filename:="N:\FOLDER1\{yyyy}\{mm-yyyy}\MyFile.xls"

Thanks for the help,

IP

 

[Mike H]

Hi,

SaveAs is an illegal sub name. Try this for your saveas line

ActiveWorkbook.SaveAs Filename:="N:\FOLDER1\" & Range("A2").Value &
Range("A3").Value & "\MyFile.xls"

Mike

 

[Mike]

yyyy = Range("A2").Value
mmyyyy = Range("A3").Value
ActiveWorkbook.SaveAs Filename:="C:\FOLDER1\" & yyyy & "\" & mmyyyy &
"\MyFile.xls"

 

[iperlovsky]

Range("A2") is a date and I want to use just the year from it for yyyy. I
tried this Text(Year(Range("B6").Value), "0000") but received an error.

Also, Range("A3") is a date which I want to be month-year for mmyyyy. I
tried this Text(Range("B6").Value), "mm-yyyy") but received an error.

any suggestions?

 

[Justin Larson]

My first attempt at a simple VBA action. This post made it possible. Was
trying to figure out how to save as based on the value in a cell, and this
did the trick.

Thanks a ton.

-Liver

 

[Mike]

Sub doit()
yyyy = Format(Range("A2").Value, "yyyy")
mmyyyy = Format(Range("A3").Value, "mm-yyyy")
ActiveWorkbook.SaveAs Filename:="C:\FOLDER1\" & yyyy & "\" & mmyyyy &
"\MyFile.xls"
End Sub

 

[Per Jessen]

Try this:

MyYear = Year(Range("A2").Value)
mmYY = Format((Range("A3").Value), "mm-yyyy")
MySaveAsFileName = "N:\FOLDER1\" & MyYear & "\" & mmYY & "\MyFile.xls"
ActiveWorkbook.SaveAs Filename:=MySaveAsFileName

Regards,
Per