Assigning value and refering it back

Question

in a macro i am saving a particular cell as value/date. now I want to refer this cell downstream in the macro.I also might be using this to save the file.i.e say a cell A01 has value as xxx. I want to give a name to the cell A01 and use it downstream. How do i do that within a macro. how should it be defined initially and also what is the procedure to call it when saving the file within a macro?

Answer

Rajeev

The following macro will add a worksheet name, "test", to cell a1 on sheet1.

Sub Macro1()

   ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:="=Sheet1!R1C1"

End Sub

Sub Macro2()

    ActiveWorkbook.SaveAs Filename:=range("test").value, _
       FileFormat:=xlOpenXMLWorkbookMacroEnabled

End sub

will save a file with the file name equal to the value in the range named "test". The value has to be a valid file name or you will get an error.