VBA: How to make the cell equals to another cell from different Worksheet using ActiveSheet?

[salif_gottem]

I have a workbook that contains about 50 worksheets with new and deleted worksheets deleted every week, one worksheet, one subcontractor.
Every time I add a new one, I have a VBA Code that create another worksheet with a new name. This is the code :

Sub AddFile()
   
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Sheets("Nouvelle Fiche")
Dim newws As Worksheet, sh As Worksheet, newname
Dim query As Long, xst As Boolean, info As String

retry:
xst = False
newname = Application.InputBox("Entrez le nom de votre nouvelle fiche:", info, , , , , , 2)
If newname = "False" Then Exit Sub
For Each sh In wb.Sheets
If sh.Name = newname Then
xst = True: Exit For
End If
Next
If Len(newname) = 0 Or xst = True Then
info = "Le nom de votre nouvelle fiche est invalide."
 GoTo retry
End If
    
End Sub

Now, I need a resume page where there is almost every important information without looking deep into each worksheet.
Why? Because co-workers get lost in all the information when they are looking for one subcontractor (too much legal information involved in too, so it's better less person that can see it.)

So, I made this code :

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

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
ActiveSheet.Range("P3").Copy
ws.Cells(i, 2).PasteSpecial xlPasteValues
End If
Next
  
Application.CutCopyMode = False
 
End Sub

My problem is that I don't want to copy and paste because if I modify a worksheet I will not see the modifications on the resume page…
I need to make the cells from the resume page equals to the specific worksheets cells.

But my VBA Code works with ActiveSheet so I cannot use :

Sheets("Résumé").Range("B3").Formula = "='SheetName'!P3"

 

[sijpie]

Sheets("Résumé").Range("B3").Formula = "='" & ActiveSheet.Name & "'!P3"

 

[sijpie]

As an aside: Copy/Paste is very slow in VBA. If you want to copy values to another location, don't use .Copy / .Pastespecial. Just set the value the same:

'    Keep code inside loops efficient!
    Dim vP3 As Variant
    vP3 = ActiveSheet.Range("P3").Value ' Read this value only once into variable. Reading values off the sheet is relatively slow. In a loop this becomes noticeable!
    
    For i = 1 To 200
        If ws.Cells(i, 1) = vP3 Then
        'Copy / Paste is inefficient
''            ActiveSheet.Range("P3").Copy
''            ws.Cells(i, 2).PasteSpecial xlPasteValues
            ws.Cells(i, 2) = vP3
            
        End If
    Next i