Bonjour dhany MFerrand et le forum
Voici mon code lorsque je passe à l'année suivante:
Option Explicit
Sub NouvelleAnnee()
Dim NomFeuille As String
Dim An As Integer
Dim Couleur
Dim Nom As String
Application.ScreenUpdating = False
Couleur = Array(3, 5, 43, 6, 7, 33, 29, 27, 38, 46, 26, 6)
With ActiveSheet
An = Val(Split(.Name, " ")(1))
If An = 0 Then
MsgBox "Nom de La Feuille non Conforme"
Exit Sub
End If
.Unprotect
NomFeuille = "Année " & An + 1
.Copy after:=Sheets(Sheets.Count)
'.Shapes("AnneePlus").Delete 'Mettre en commentaires pour ne pas effacer le bouton (nouvelle année)de la Feuille Précédente
.Protect
Nom = .Name
End With
With ActiveSheet
.Name = NomFeuille
.Tab.ColorIndex = Couleur((An - 2000) Mod 12)
.Range("H4:I15,E4:E15,F4:F15").ClearContents
.Cells.Replace What:=An, Replacement:=An + 1
.Cells.Replace What:=An - 1, Replacement:=An
With .Range("A1")
.Characters(Start:=8, Length:=1).Font.ColorIndex = 15
.Characters(Start:=17, Length:=1).Font.ColorIndex = 15
.Characters(Start:=28, Length:=1).Font.ColorIndex = 15
.Characters(Start:=34, Length:=1).Font.ColorIndex = 15
.Characters(Start:=35, Length:=4).Font.ColorIndex = 3
End With
With .Range("A3")
.Characters(Start:=17, Length:=4).Font.ColorIndex = 3
End With
With .Range("B2")
.Characters(Start:=24, Length:=4).Font.ColorIndex = 3
End With
With .Range("C2")
.Characters(Start:=24, Length:=4).Font.ColorIndex = 3
End With
With .Range("D2")
.Characters(Start:=22, Length:=4).Font.ColorIndex = 3
.Characters(Start:=29, Length:=4).Font.ColorIndex = 3
End With
With .Range("G1")
.Characters(Start:=7, Length:=1).Font.ColorIndex = 35
.Characters(Start:=15, Length:=1).Font.ColorIndex = 35
.Characters(Start:=21, Length:=1).Font.ColorIndex = 35
.Characters(Start:=22, Length:=4).Font.ColorIndex = 3
End With
With .Range("G2")
.Characters(Start:=14, Length:=4).Font.ColorIndex = 3
End With
With .Range("H2")
.Characters(Start:=14, Length:=4).Font.ColorIndex = 3
End With
With .Range("J2")
.Characters(Start:=44, Length:=4).Font.ColorIndex = 3
.Characters(Start:=51, Length:=4).Font.ColorIndex = 3
End With
With .Range("G16")
.Characters(Start:=14, Length:=5).Font.ColorIndex = 3
End With
.Range("A1").Select
End With
End Sub