Wanting to send a formula to a Cell using VBA and a variable sheet name defined with code.

Hello All,

Well, It's been a long time but using books, Google, Forum searches and a lot of trial / error, I think I'm on my way to figuring this Excel and VBA out. The more I can see it coming together and working, the more motivating it is. Last night I came across an issue that I cannot seem to find the solution to, and I know it's in the syntax of the code. I am trying to use VBA to send a formula to a Cell using a variable sheetname defined as a string. My workbook has several sheets, each representing different years. So, If "X" didn't happen this year, then return the value from last year. The formula is a Lookup statement inside an If statement returning the corresponding date.

The formula in the Cell Looks like:

=IF(D11=0,'2012 Attnd'!E11,LOOKUP(2,1/($AJ11:$OJ11="X"),$AJ$5:$OJ$5))

Recording this as a macro gives me the following VBA code:

ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]=0,'2012 Attnd'!RC,LOOKUP(2,1/(RC36:RC400=""X""),R5C36:R5C400))"

My goal is to replace '2012 Attnd'! with the variable LastYearSheet as defined in my VBA code. The variable is set as a string and holds the correct value. I am just struggling with the number of quote marks, apostrophe marks and maybe an ampersand or two.

Once again, I thank you all for your time and assistance. I look forward to continuing my VBA education as the more I get into it the more motivating it becomes.

Scott