Passing contents of a cell to a macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a worksheet model which will be used repetitively. I have the
macro to start the new worksheet and pre-fill some of the cells. After
manually completing the rest of the information on the worksheet, I want to
run a macro which will save that worksheet to a filename that exists in one
of the cells.

Here is the SaveAs Filename macro:

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\myusername\My Documents\filename.xls",
FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

I want to be able to replace "filename" with the value in cell A5 for
instance.

How can I do this?

Thank you.
Learning In Florida
 
mycellfilename=Range("A5").Value

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\myusername\My Documents\" &
mycellfilename & ".xls",
FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
 
Try range("A1").value if the path and filename are in the cell. If
only the filename is in the cell, you can probably use something like:

"C:\Documents and Settings\myusername\My Documents\" &
range('A1").value

This all presumes the filename is in cell A1 of the active sheet. You
could make the code more robust by naming the cell containing the
filename and referencing it by name rather than address. Something
like:

"C:\Documents and Settings\myusername\My Documents\" &
range('filename")

where filename is the name of the cell with the filename.

Good luck.

Ken
Norfolk, Va
 
Both answers I have received LOOK good, but they don't seem to be working.
I'm getting "expression expected" errors. I suspect the concatenation
character. Perhaps I need more quotes or parentheses, but try as I may, I'm
not getting past the errors. I've eliminated the spaces between the
concatenated operands to no avail. I've added parens to no avail. Should I
be using single of double quotes? 'Tis a puzzlement!
 
Try declaring a variable, then building the filename into the
variable, and finally using the variable in the Activeworkbook.saveas
line. You can also get rid of a lot of parameters since you are using
the defaults. Something like:

Sub Macro1()

Dim fn As String
fn = "C:\Documents and Settings\myusername\My Documents\filename.xls"
ActiveWorkbook.SaveAs Filename:=fn

End Sub

You can use debug.print or a msgbox to verify that fn is a valid
filename and that it is what you want.

Good luck.

Ken
 
Back
Top