Text from a cell to a macro

Keith.Jackson

Board Regular
Joined
Mar 31, 2005
Messages
115
How can I pull text from a cell in a worksheet and use that text to name a file in a sub routine? For example , the name Bill Smith is in a cell in a worksheet but sometimes that cell is another name. I want to set up a macro to e mail a file to someone with the name in that cell as the file being e mailed.

Thanks
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
You will have some syntax that says "Sendto" or similar, like this:

.sendto "Bill Smith"

replace "Bill Smith" with [a1].text, where [a1] contains the name

eg

.sendto [a1].text
 

Keith.Jackson

Board Regular
Joined
Mar 31, 2005
Messages
115
How can I rename a file created in a macro based on a name in text in a cell on a worksheet?
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
Record a macro to "save as" a name:

The look at the macro and replace the name in quotes with:
sheets("Sheet1").range("a1").text
Leave the ".xls" in brackets and use ampersands

You'll end up with seomething like this:

ActiveWorkbook.SaveAs "c:\" & .sheets("Sheet1").range("a1").text & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Hope that helps
 

Watch MrExcel Video

Forum statistics

Threads
1,118,811
Messages
5,574,452
Members
412,595
Latest member
slim313
Top