How or what is the vba syntax if i want the user to select a atrget sheet for
a macro as an input?

What about having your user kick off the macro from the sheet he/she wants
it to operate on? Then you can use ActiveSheet inside your macro to
reference it.

Rick


"Yossi evenzur" <(E-Mail Removed)> wrote in message
news:7BCFFE5F-AD1B-421A-BBD3-(E-Mail Removed)...
> How or what is the vba syntax if i want the user to select a atrget
> sheet for a macro as an input?

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" <(E-Mail Removed)> wrote in message
news:7BCFFE5F-AD1B-421A-BBD3-(E-Mail Removed)...
> How or what is the vba syntax if i want the user to select a atrget
> sheet for a macro as an input?

Hi
It dosen't matter, the problem is that both the target and source sheets
have different names each time. in general i want to open an excel workbook
with two sheets (different names each time and no i need these names to stay
as they are) in the first sheet i add row and vlookup at the second sheet.
any better ideas?

"Rick Rothstein (MVP - VB)" wrote:

> What about having your user kick off the macro from the sheet he/she wants
> it to operate on? Then you can use ActiveSheet inside your macro to
> reference it.
>
> Rick
>
>
> "Yossi evenzur" <(E-Mail Removed)> wrote in message
> news:7BCFFE5F-AD1B-421A-BBD3-(E-Mail Removed)...
> > How or what is the vba syntax if i want the user to select a atrget
> > sheet for a macro as an input?
>
>

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" <(E-Mail Removed)> wrote in message
> news:7BCFFE5F-AD1B-421A-BBD3-(E-Mail Removed)...
> > How or what is the vba syntax if i want the user to select a atrget
> > sheet for a macro as an input?
>
>

Dim wks as worksheet

set wks = nothing
on error resume next
set wks = application.inputbox _
(Prompt:="please click on a cell in the worksheet to use", type:=8).parent
on error resume next

if wks is nothing then
'user hit cancel, what should happen
else
msgbox wks.name
end if

You may want to consider creating a userform that displays the worksheet names
(in comboboxes??) and allow the user to select the sheet from that list.

If you want to try, take a look at the userform in Myrna Larson and Bill
Manville's compare program:

http://www.cpearson.com/excel/whatsnew.htm
look for compare.xla

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

--

Dave Peterson

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" <(E-Mail Removed)> wrote in message
news:B4B84C93-5DFA-4535-8531-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:7BCFFE5F-AD1B-421A-BBD3-(E-Mail Removed)...
>> > How or what is the vba syntax if i want the user to select a atrget
>> > sheet for a macro as an input?
>>
>>