refer to worksheet by index number in formula
 

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

refer to worksheet by index number in formula
 

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

"Aaron" ...
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

refer to worksheet by index number in formula
 

Thanks, Nick, worked perfectly.

"NickHK" wrote:

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

"Aaron" ...
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