Option Explicit
Option Base 1
Public Const Feuille_Modele = "Modele", Feuille_Recap = "Récap"
Public Const Recap_Liste_Colonne = 2, Recap_Liste_Ligne = 8
Public Const Feuille_Modele_Initiale = "Q1"
'
Function Test_Presence_Feuille(ByVal Nom_feuille As String) As Boolean
Dim folio As Worksheet
Test_Presence_Feuille = False
For Each folio In Worksheets
If folio.Name = Nom_feuille Then Test_Presence_Feuille = True: Exit Function
Next folio
End Function
Sub analyse_et_ajout_feuille()
Dim lig As Long, nb_lig As Long
Dim entree As Variant
Worksheets(Feuille_Recap).Activate
nb_lig = UBound(ActiveSheet.UsedRange.Value, 1)
entree = ActiveSheet.Range(Cells(Recap_Liste_Ligne, Recap_Liste_Colonne), Cells(nb_lig, Recap_Liste_Colonne)).Value
nb_lig = UBound(entree, 1)
For lig = 1 To nb_lig
If entree(lig, 1) <> "" Then
If Not Test_Presence_Feuille(entree(lig, 1)) Then
Sheets("Modele").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = entree(lig, 1)
ActiveSheet.Range(Feuille_Modele_Initiale).Value = entree(lig, 1)
End If
End If
Next lig
End Sub