VBA use sheet codename versus sheet name in formula

[FryGirl]

In VBA, I understand the difference between the sheet codename, sheet name, and sheet index number and how to use those differences in VBA, but can't quite figure out how to use the codename in the formula.

.FormulaR1C1 = "=VLOOKUP(RC25,Sheet1!C[-65]:C[-64],2,FALSE)"

Sheet1 is the sheet name, but the codename is Sheet11.

Any thoughts?

 

[RoryA]

You can't use code names in formulas. You could use the codename to get the name, like this:

.FormulaR1C1 = "=VLOOKUP(RC25,'" & Sheet11.Name & "'!C[-65]:C[-64],2,FALSE)"

 

[FryGirl]

Thanks Rory. Works well.