hi Ardy,

ActiveCell.FormulaR1C1 = "=('" & TabName & "'!R[2]C)"

or

ActiveCell.Formula = "=('" & TabName & "'!A8)"


--
isabelle



Le 2011-12-12 17:22, Ardy a écrit :
I have the Following Code. I understand it is not optimized, the
question is how do i place the captured tab name in the formula. what
I am trying is not working.

Public Sub Tester001()

Dim strClickedSht As String
Dim TabName As String

Sheets("Unit 2 Week 2 Test Standards An").Select
strClickedSht = ActiveSheet.Name
TabName = strClickedSht
MsgBox TabName 'for testing
Sheets("Standard Analysis").Select
Range("A6").Select
ActiveCell.FormulaR1C1 = "=('TabName'!R[2]C)"
End Sub