création feuille dans fichier excel

Private Sub Worksheet_Change(ByVal Target As Range)
'Feuil2 est le CodeName de la feuille modèle
Dim r As Range
Set r = Intersect(Target, [C:C], Me.UsedRange)
If r Is Nothing Then Exit Sub
Application.ScreenUpdating = False
On Error Resume Next 'si la feuille à créer n'existe pas
For Each r In r
  If UCase(r.Text) = "R" And r(1, 0).Text <> "" Then
    If IsError(Sheets(Left(r(1, 0).Text, 31))) Then
      Feuil2.Copy After:=Sheets(Sheets.Count)
      With Sheets(Sheets.Count)
        .Name = Left(r(1, 0).Text, 31)
        .[G1] = .Name
        .Visible = True
      End With
    Else
      Sheets(Left(r(1, 0).Text, 31).Visible = True
    End If
  End If
Next
Me.Activate
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Feuil2 est le CodeName de la feuille modèle
Dim r As Range, w As Worksheet
Set r = Intersect(Target, [C:C], Me.UsedRange)
If r Is Nothing Then Exit Sub
Application.ScreenUpdating = False
On Error Resume Next 'si la feuille à créer n'existe pas
For Each r In r
  If UCase(r.Text) = "R" And r(1, 0).Text <> "" Then
    If IsError(Sheets(Left(r(1, 0).Text, 31))) Then
      Feuil2.Copy After:=Sheets(Sheets.Count)
      With Sheets(Sheets.Count)
        .Name = Left(r(1, 0).Text, 31)
        .[G1] = .Name
      End With
    End If
  End If
Next
'---masquage des feuilles---
For Each w In Worksheets
  If w.Name <> Me.Name Then w.Visible = False
Next
'---démasquage des feuilles notées "R"---
For Each r In [C:C].SpecialCells(xlCellTypeConstants)
  If UCase(r) = "R" Then _
    Sheets(Left(r(1, 0).Text, 31)).Visible = True
Next
Me.Activate
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Feuil2 est le CodeName de la feuille modèle
Dim r As Range, w As Worksheet
Set r = Intersect(Target, [C:C], Me.UsedRange)
If r Is Nothing Then Exit Sub
Application.ScreenUpdating = False
On Error Resume Next 'si la feuille à créer n'existe pas
For Each r In r
  If UCase(r.Text) = "R" And r(1, 0).Text <> "" Then
    If IsError(Sheets(Left(r(1, 0).Text, 31))) Then
      Feuil2.Visible = True 'affiche la feuille modèle
      Feuil2.Copy After:=Sheets(Sheets.Count)
      With Sheets(Sheets.Count)
        .Name = Left(r(1, 0).Text, 31)
        .[G1] = .Name
      End With
    End If
  End If
Next
'---masquage des feuilles---
For Each w In Worksheets
  If w.Name <> Me.Name Then w.Visible = False
Next
'---démasquage des feuilles notées "R"---
For Each r In [C:C].SpecialCells(xlCellTypeConstants)
  If UCase(r) = "R" Then _
    Sheets(Left(r(1, 0).Text, 31)).Visible = True
Next
Me.Activate
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Feuil2 et Feuil3 sont les CodeName des feuilles modèles
Dim r As Range, w As Worksheet
Set r = Intersect(Target, [C:C], Me.UsedRange)
If r Is Nothing Then Exit Sub
Application.ScreenUpdating = False
On Error Resume Next 'si la feuille à créer n'existe pas
For Each r In r
  If UCase(r.Text) = "R" Then
    If IsError(Sheets(Left(r(1, 0).Text, 31))) Then
      Set w = Nothing
      If Left(r(1, 0), 2) = "BC" Then Set w = Feuil2
      If Left(r(1, 0), 1) = "D" Then Set w = Feuil3
      If Not w Is Nothing Then
        w.Visible = True 'affiche la feuille modèle
        w.Copy After:=Sheets(Sheets.Count)
        With Sheets(Sheets.Count)
          .Name = Left(r(1, 0).Text, 31)
          .[G1] = .Name
        End With
      End If
    End If
  End If
Next
'---masquage des feuilles---
For Each w In Worksheets
  If w.Name <> Me.Name Then w.Visible = False
Next
'---démasquage des feuilles notées "R"---
For Each r In [C:C].SpecialCells(xlCellTypeConstants)
  If UCase(r) = "R" Then _
    Sheets(Left(r(1, 0).Text, 31)).Visible = True
Next
Me.Activate
End Sub