Perfectionnement d'un planning

Sub Feuilles()
Dim i As Integer
For i = 4 To 42 ' s'il faut rajouter des feuilles commencez avant toutes choses à utiliser cette macro et à changer 42 pour le nombre de feuilles à ajouter
Sheets(i).Select
Sheets(i).Copy After:=Sheets(i)
Next i
End Sub

Cliquez pour agrandir...

Option Explicit

Sub noms()
Dim i As Long
Sheets(1).Activate
For i = 2 To 65536
If Cells(i, 3) = "Catherine" Then
Cells(i, 2) = 1
ElseIf Cells(i, 3) = "Yannick" Then
Cells(i, 2) = 2
ElseIf Cells(i, 3) = "JP" Then
Cells(i, 2) = 3
ElseIf Cells(i, 3) = "A" Then ' changer la lettre A et le nom de chaque onglet dans l'ordre ici onglet 4 aprés données
Cells(i, 2) = 4
ElseIf Cells(i, 3) = "B" Then
Cells(i, 2) = 5
ElseIf Cells(i, 3) = "C" Then
Cells(i, 2) = 6
ElseIf Cells(i, 3) = "D" Then
Cells(i, 2) = 7
ElseIf Cells(i, 3) = "E" Then
Cells(i, 2) = 8
ElseIf Cells(i, 3) = "F" Then
Cells(i, 2) = 9
ElseIf Cells(i, 3) = "G" Then
Cells(i, 2) = 10
ElseIf Cells(i, 3) = "H" Then
Cells(i, 2) = 11
ElseIf Cells(i, 3) = "I" Then
Cells(i, 2) = 12
ElseIf Cells(i, 3) = "J" Then
Cells(i, 2) = 13
ElseIf Cells(i, 3) = "K" Then
Cells(i, 2) = 14
ElseIf Cells(i, 3) = "L" Then
Cells(i, 2) = 15
ElseIf Cells(i, 3) = "M" Then
Cells(i, 2) = 16
ElseIf Cells(i, 3) = "N" Then
Cells(i, 2) = 17
ElseIf Cells(i, 3) = "O" Then
Cells(i, 2) = 18
ElseIf Cells(i, 3) = "P" Then
Cells(i, 2) = 19
ElseIf Cells(i, 3) = "Q" Then
Cells(i, 2) = 20
ElseIf Cells(i, 3) = "R" Then
Cells(i, 2) = 21
ElseIf Cells(i, 3) = "S" Then
Cells(i, 2) = 22
ElseIf Cells(i, 3) = "T" Then
Cells(i, 2) = 23
ElseIf Cells(i, 3) = "U" Then
Cells(i, 2) = 24
ElseIf Cells(i, 3) = "V" Then
Cells(i, 2) = 25
ElseIf Cells(i, 3) = "W" Then
Cells(i, 2) = 26
ElseIf Cells(i, 3) = "X" Then
Cells(i, 2) = 27
ElseIf Cells(i, 3) = "Y" Then
Cells(i, 2) = 28
ElseIf Cells(i, 3) = "Z" Then
Cells(i, 2) = 29
ElseIf Cells(i, 3) = "AA" Then
Cells(i, 2) = 30
ElseIf Cells(i, 3) = "AB" Then
Cells(i, 2) = 31
ElseIf Cells(i, 3) = "AC" Then
Cells(i, 2) = 32
ElseIf Cells(i, 3) = "AD" Then
Cells(i, 2) = 33
ElseIf Cells(i, 3) = "AE" Then
Cells(i, 2) = 34
ElseIf Cells(i, 3) = "AF" Then
Cells(i, 2) = 35
ElseIf Cells(i, 3) = "AG" Then
Cells(i, 2) = 36
ElseIf Cells(i, 3) = "AH" Then
Cells(i, 2) = 37
ElseIf Cells(i, 3) = "AI" Then
Cells(i, 2) = 38
ElseIf Cells(i, 3) = "AJ" Then
Cells(i, 2) = 39
ElseIf Cells(i, 3) = "AK" Then
Cells(i, 2) = 40
ElseIf Cells(i, 3) = "AL" Then
Cells(i, 2) = 41
ElseIf Cells(i, 3) = "AM" Then
Cells(i, 2) = 42
ElseIf Cells(i, 3) = "AN" Then
Cells(i, 2) = 43
ElseIf Cells(i, 3) = "AO" Then
Cells(i, 2) = 44
ElseIf Cells(i, 3) = "AP" Then ' si nombre de personnes supérieur à 45 continuer le elseif avec la référence suivante (46)
Cells(i, 2) = 45
Else
Exit For
End If
Next i
End Sub

Cliquez pour agrandir...

Sub Feuilles()
Dim i As Integer
Application.ScreenUpdating = False
For i = 4 To 48 ' changer le nombre d'onglets (48)s'il y a lieu suivant le nombre de personnes onglet "données" colonne S
Sheets(i).Select
Sheets(i).Copy After:=Sheets(i)
Next i
Application.ScreenUpdating = True
End Sub

Sub onglets()
Dim i As Integer
Dim j As Long
Application.ScreenUpdating = False
On Error GoTo erreur
For j = 2 To 65536
Sheets(1).Activate
For i = 5 To 49 ' changer le nombre d'onglets (49)s'il y a lieu suivant le nombre de personnes onglet "données" colonne S
If Sheets(1).Cells(j, 19) = "" Then Exit Sub
If Sheets(i).Name = "" Then Exit Sub
If Sheets(i).Name = Sheets(1).Cells(j, 19).Text Then
Sheets(i).Select
Else
Sheets(i).Name = Sheets(1).Cells(j + i - 5, 19)
Sheets(i).Range("A2") = Sheets(1).Cells(j + i - 5, 19)
Cells(j, 19).Select
End If
Next i
Next j
erreur: Exit Sub
Application.ScreenUpdating = True
End Sub

Cliquez pour agrandir...