VBA: Having the Sheet name in a range reference as a variable

[Mrnutle]

I am writing a macro to populate a table. Each line in the table will show the worksheet name in column A, and reference cell N5 of that particular worksheet in column B.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Column A is the easy part. IF I record a macro of what should go in coulm "B", i get something like this<o></o>
<o></o>
ActiveCell.FormulaR1C1 = "=Building100!R[1]C[12]"<o></o>
Where "Building100" is my sheet name<o></o>
What I need is the sheet name par tof the reference to look at the cell in column "A" (Which says "Building100")<o></o>
<o></o>
I tried this<o></o>
<o></o>
Dim BldgSheet as String<o></o>
BldgSheet = Range("A4")<o></o>
Range("B4").Select<o></o>
ActiveCell.FormulaR1C1 = "=BldgSheet!R[1]C[12]"<o></o>
<o></o>
I know this sytax is wrong, because this just causes the algoryth to look for a sheet named "BldgSheet". I have actually tried a bunch of other ways, I just don't know enough to make this work.<o></o>
<o> </o>

 

[VoG]

Try

ActiveCell.FormulaR1C1 = "=" & BldgSheet & "!R[1]C[12]"

 

[Mrnutle]

Awsome, that works.

Do you mind going into detail about the formatting and why this works.

Do the extra quotes and ampersand designate BldgSheet as a variable?

 

[VoG]

As BldgSheet is a variable you have to take it out of the quotes or Excel will assume it is part of the string, not a variable.