XL 2013 reprendre des cellules d'une feuille dans une autre

Private Sub Worksheet_Activate()
[A1] = [A1] 'entraîne le recalcul de toutes les formules
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheet_Activate 'lance la macro
End Sub

Private Sub Worksheet_Activate()
Dim F As Worksheet, h%, ncol%, nlig&, tablo, nom$, i&, j%, k%, dat As Variant, P As Range, n%, c As Range
Set F = Sheets("planG")
h = 12
ncol = 14
With [A1] '1ère cellule du tableau
    nlig = Cells(Rows.Count, .Column).End(xlUp).Row - .Row + 1 'sur la dernière ligne
    nlig = Application.Ceiling(nlig, h + 2) 'ajustement
    tablo = .Resize(nlig, ncol).Formula 'matrice, plus rapide
    nom = tablo(1, 1)
    nom = Application.VLookup(nom, Sheets("COLLEGUES").Columns("A:B"), 2, 0) 'initiale
    For i = 2 To UBound(tablo) Step h + 2 'lignes des dates
        For j = 1 To ncol Step 2
            For k = 1 To h
                tablo(i + k, j) = "" 'RAZ
                tablo(i + k, j + 1) = "" 'RAZ
            Next k
            dat = Evaluate(tablo(i, j)) 'évalue la formule
            If IsDate(dat) Then dat = CLng(CDate(dat))
            If IsNumeric(dat) Then
                Set P = F.Columns(Application.Match(dat, F.Rows(5), 0)).Resize(, 3)
                n = Application.CountIf(P, nom) 'NB.SI
                Set c = P.Cells(1)
                For k = 1 To IIf(n > h, h, n)
                    Set c = P.Find(nom, c, xlValues, xlWhole)
                    tablo(i + k, j) = F.Cells(c.Row, 1)
                    tablo(i + k, j + 1) = F.Cells(6, c.Column)
                Next k
            End If
    Next j, i
    '---restitution---
    Application.EnableEvents = False 'désactive les évènements
    .Resize(nlig, ncol) = tablo
    Application.EnableEvents = True 'réactive les évènements
End With
End Sub