1) You would use this formula...

ActiveCell.FormulaR1C1 = "=VLOOKUP(R[1]C," & Answer &
"!R2C1:R255C3,3,FALSE)"

2) If you had them click on the sheet tab, then that sheet would be selected
and you would have the situation I mentioned in my previous reply, only
delayed slightly... ActiveSheet would tell you what sheet they were on. Here
is another thought... if you know they will have to change sheets to make
your macro work (that is, the look up data is never going to be on the page
they are on when the macro is launched, then perhaps you could make use of
the Workbook's SheetActivate event to get recognize the new sheet had been
selected and get the sheet name from its Sh argument?

Rick


"Yossi evenzur" wrote in message
...
Hi Rick
Please take a look at the following:
1)
Answer = InputBox("Tell me a sheet name")

'the Answer is"XXXX"

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[1]C,'"What do I put HERE ???"'!R2C1:R255C3,3,FALSE)"

2) why can't i simply (simply?) click on the sheet?

"Rick Rothstein (MVP - VB)" wrote:

Another possibility... Execute this statement in your macro at the point
you
want to get the sheet name...

Answer = InputBox("Tell me a sheet name")

and then reference that sheet in your code using this Worksheets(Answer).
As
an example...

Worksheets(Answer).Range("D4").Value = "Hello"

You will probably need to put some error checking code in just in case
your
user types the sheet name in wrong though.

Rick


"Yossi evenzur" wrote in message
...
How or what is the vba syntax if i want the user to select a atrget
sheet for a macro as an input?