refer to worksheet by index number in formula

[Guest]

Hi

I'm trying to have a Vlookup formula inserted by a macro, but if possibly I
want to refer to the range by worksheet index number - the sheet will always
be in the same spot, but won't always be called the same thing. The line is a
simple one:
ActiveCell.FormulaR1C1 = "=Vlookup(RC[-1],'Sheet2'!R1C1:R100C19,11)". Is
there a way to refer to Sheet2 by it's index number, or another way to set up
the formula so it works?

Thanks

 

[NickHK]

Aaron,
As you are using a macro to build your formula string, you can get the name
from the index
ActiveCell.FormulaR1C1 = "=Vlookup(RC[-1],'" & Worksheets(1).Name &
"'!R1C1:R100C19,11)"

NickHK

 

[Guest]

Thanks, Nick, worked perfectly.

Aaron,
As you are using a macro to build your formula string, you can get the name
from the index
ActiveCell.FormulaR1C1 = "=Vlookup(RC[-1],'" & Worksheets(1).Name &
"'!R1C1:R100C19,11)"

NickHK

Hi

I'm trying to have a Vlookup formula inserted by a macro, but if possibly
I
want to refer to the range by worksheet index number - the sheet will
always
be in the same spot, but won't always be called the same thing. The line
is a
simple one:
ActiveCell.FormulaR1C1 = "=Vlookup(RC[-1],'Sheet2'!R1C1:R100C19,11)". Is
there a way to refer to Sheet2 by it's index number, or another way to set
up
the formula so it works?

Thanks