Can't use ActiveSheet in a formula-CodePudding

I tried to insert my ActiveSheet name inside a formula. Still doesn't understand why it didn't worked...

Sub AddResume()

    Dim ws As Worksheet: Set ws = Worksheets("Résumé")
    Dim LastRow As Long: LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
    Dim namee As String: namee = ActiveSheet.Name
    
     ActiveSheet.Range("B1").Copy
     ws.Cells(LastRow, 1).PasteSpecial xlPasteValues

Application.CutCopyMode = False
 
    For i = 1 To 200
       If ws.Cells(i, 1) = ActiveSheet.Range("P3").Value Then
          ws.Cells(i, 2).FormulaR1C1 = "=""&namee&"!P3"
       End If
    Next
  
Application.CutCopyMode = False
 

End Sub

CodePudding user response：

You have to put the sheets variable name into single quotes

ws.cells(i,2).Formula = "='" & namee & "'!P3"

And as you are referencing a cell - you don't need FormulaR1C1 which is for a relative formula.