Excel Sheet Codenames in Formula VBA

Question

I'm trying to create a formula in VBA to match some rows. Currently I have something like:

ActiveCell.FormulaR1C1 = _
"=INDEX('Report 2'!C[4],MATCH(Report!RC[2],'Report 2'!C[8],0))"

Where the sheet code name for Report is Sheet1 and sheet code name for Report 2 is Sheet2. So I would like to change the above code to something like:

ActiveCell.FormulaR1C1 = _
"=INDEX(Sheet2!C[4],MATCH(Sheet1!RC[2],Sheet2!C[8],0))"

Is this possible? It tried searching but no luck.

Thanks!

what do you mean by "code name"? is it a variable holding the real name of your sheet? — Barranka
Sheet code name which is internally available in Excel VBA code. See this — tijuthomas
then it is just a matter of concatenating the string that builds the formula, as John Bustos proposes in his answer — Barranka

John Bustos John Bustos · Accepted Answer · 2013-05-07T21:18:35

... This should do it:

ActiveCell.FormulaR1C1 = "=INDEX('" & Sheet2.Name & "'!C[4],MATCH(" & Sheet1.Name & "!RC[2],'" & Sheet2.Name & "'!C[8],0))"

Excel Sheet Codenames in Formula VBA

1 Answers