Récupération donnée autres feuilles

Private Sub Worksheet_Change(ByVal Target As Range)
    If Union(Target, Range("FOURNITURES")).Address = Range("FOURNITURES").Address And Target.Count = 1 And Not IsEmpty(Target) Then
        'Vérifier si la feuille existe
        Dim sh As Worksheet
        Set sh = GetSheetByName(Target.Text)
        
        'Si elle n'existe pas demander s'il faut la créer
        If sh Is Nothing Then
            If MsgBox("La feuille '" & Target.Text & "' n'existe pas." & vbCrLf & _
                        "Voulez-vous la créer?", vbQuestion + vbYesNo, "Création feuille") = vbYes Then
                
               Sheets("Modele").Copy After:=Target.Parent
               With ActiveSheet
                    .Visible = True
                    .Name = Target
                    .Range("B4") = Target
                End With
            End If
            
            'Après la création réactiver cette feuille ('Tableau stocks') puis mettre les formules
          [COLOR=red]  On Error Resume Next
            Application.EnableEvents = False
            With Target
                .Parent.Activate
                .Offset(, 1).Formula = "=INDEX(INDIRECT(""'"" & " & Target.Address(False, True) & " & ""'!$C$9:$K$44""),MATCH(9^9,Dates,1),1)"
                .Offset(, 2).Formula = "=INDEX(INDIRECT(""'"" & " & Target.Address(False, True) & " & ""'!$C$9:$K$44""),MATCH(9^9,Dates,1),8)"
                .Offset(, 3).Formula = "=INDEX(INDIRECT(""'"" & " & Target.Address(False, True) & " & ""'!$C$9:$K$44""),MATCH(9^9,Dates,1),9)"
            End With
            Application.EnableEvents = True
            On Error GoTo 0
[/COLOR]            
            'Ajouter l'hyperlien dans la feuille Accueil
            Dim c As Range
            With Sheets("Accueil")
                Set c = .Range("C" & .Rows.Count).End(xlUp)(2)
                c = Target.Text
                .Hyperlinks.Add Anchor:=c, Address:="", SubAddress:="'" & Target.Text & "'" & "!A1", TextToDisplay:=Target.Text
            End With
        End If
    End If
End Sub

=SI(ESTVIDE($B9);"";INDEX(INDIRECT("'"&$B9&"'!$C$9:$K$44");EQUIV(9^9;Dates;1);1))

            With Target
                .Parent.Activate
                .Offset(, 1).Formula = "=IF(ISBLANK($B" & Target.Row & "),"""",INDEX(INDIRECT(""'"" & " & Target.Address(False, True) & " & ""'!$C$9:$K$44""),MATCH(9^9,Dates,1),1))"
                .Offset(, 2).Formula = "=IF(ISBLANK($B" & Target.Row & "),"""",INDEX(INDIRECT(""'"" & " & Target.Address(False, True) & " & ""'!$C$9:$K$44""),MATCH(9^9,Dates,1),8))"
                .Offset(, 3).Formula = "=IF(ISBLANK($B" & Target.Row & "),"""",INDEX(INDIRECT(""'"" & " & Target.Address(False, True) & " & ""'!$C$9:$K$44""),MATCH(9^9,Dates,1),9))"
            End With