Sub DupliquerPlanningFix(RangDuJour As Integer) 'DetailPlanning As Integer,
'
Dim LigSource As Long, ColSource As Long, MaDate As Long
Dim ColDest As Long
Application.ScreenUpdating = False
Dim i As Variant 'car il est préférable de l'utiliser dans une boucle for each
Dim ligne As Integer
Dim cel As Range
For Each i In Range("3:98")
If i = UsfEffectif.TxtNom.Value Then 'UsfEffectif.TxtNom
Set cel = Cells.Find(what:=i)
ligne = cel.Row 'pour renvoyer le numéro de ligne
Rows(ligne).Select 'activer la igne retrouvée
End If
Next
LigSource = ligne
MaDate = CDate(UsfEffectif.TxtDateFixe) 'Application.WorksheetFunction.XLookup("UsfEffectif.TxtNom", Range("TbEffectif[Nom]"), Range("TbEffectif[Date début Fixe]"), 0, 1) 'CDate(UsfEffectif.TxtDateFixe)"
ColSource = Application.Match(MaDate, Sheets("Archives").Range("2:2"), 0) + RangDuJour - 1
With Sheets("Archives")
'Nom
.Cells(LigSource, 7 + ColSource) = .Cells(LigSource, ColSource).Value: .Cells(LigSource, 14 + ColSource) = .Cells(LigSource, ColSource).Value: .Cells(LigSource, 21 + ColSource) = .Cells(LigSource, ColSource).Value 'nom
.Cells(LigSource, 28 + ColSource) = .Cells(LigSource, ColSource).Value: .Cells(LigSource, 35 + ColSource) = .Cells(LigSource, ColSource).Value: .Cells(LigSource, 42 + ColSource) = .Cells(LigSource, ColSource).Value 'nom
.Cells(LigSource, 49 + ColSource) = .Cells(LigSource, ColSource).Value: .Cells(LigSource, 56 + ColSource) = .Cells(LigSource, ColSource).Value: .Cells(LigSource, 63 + ColSource) = .Cells(LigSource, ColSource).Value 'nom
.Cells(LigSource, 70 + ColSource) = .Cells(LigSource, ColSource).Value: .Cells(LigSource, 77 + ColSource) = .Cells(LigSource, ColSource).Value: .Cells(LigSource, 84 + ColSource) = .Cells(LigSource, ColSource).Value 'nom
.Cells(LigSource, 91 + ColSource) = .Cells(LigSource, ColSource).Value: .Cells(LigSource, 98 + ColSource) = .Cells(LigSource, ColSource).Value: .Cells(LigSource, 105 + ColSource) = .Cells(LigSource, ColSource).Value 'nom
.Cells(LigSource, 112 + ColSource) = .Cells(LigSource, ColSource).Value: .Cells(LigSource, 119 + ColSource) = .Cells(LigSource, ColSource).Value: .Cells(LigSource, 126 + ColSource) = .Cells(LigSource, ColSource).Value 'nom
.Cells(LigSource, 133 + ColSource) = .Cells(LigSource, ColSource).Value
'Semaine Type
.Cells(1 + LigSource, 7 + ColSource) = .Cells(1 + LigSource, ColSource).Value: .Cells(1 + LigSource, 14 + ColSource) = .Cells(1 + LigSource, ColSource).Value: .Cells(1 + LigSource, 21 + ColSource) = .Cells(1 + LigSource, ColSource).Value 'nom
.Cells(1 + LigSource, 28 + ColSource) = .Cells(1 + LigSource, ColSource).Value: .Cells(1 + LigSource, 35 + ColSource) = .Cells(1 + LigSource, ColSource).Value: .Cells(1 + LigSource, 42 + ColSource) = .Cells(1 + LigSource, ColSource).Value 'nom
.Cells(1 + LigSource, 49 + ColSource) = .Cells(1 + LigSource, ColSource).Value: .Cells(1 + LigSource, 56 + ColSource) = .Cells(1 + LigSource, ColSource).Value: .Cells(1 + LigSource, 63 + ColSource) = .Cells(1 + LigSource, ColSource).Value 'nom
.Cells(1 + LigSource, 70 + ColSource) = .Cells(1 + LigSource, ColSource).Value: .Cells(1 + LigSource, 77 + ColSource) = .Cells(1 + LigSource, ColSource).Value: .Cells(1 + LigSource, 84 + ColSource) = .Cells(1 + LigSource, ColSource).Value 'nom
.Cells(1 + LigSource, 91 + ColSource) = .Cells(1 + LigSource, ColSource).Value: .Cells(1 + LigSource, 98 + ColSource) = .Cells(1 + LigSource, ColSource).Value: .Cells(1 + LigSource, 105 + ColSource) = .Cells(1 + LigSource, ColSource).Value 'nom
.Cells(1 + LigSource, 112 + ColSource) = .Cells(1 + LigSource, ColSource).Value: .Cells(1 + LigSource, 119 + ColSource) = .Cells(1 + LigSource, ColSource).Value: .Cells(1 + LigSource, 126 + ColSource) = .Cells(1 + LigSource, ColSource).Value 'nom
.Cells(1 + LigSource, 133 + ColSource) = .Cells(1 + LigSource, ColSource).Value
'Absence AM
.Cells(2 + LigSource, 7 + ColSource) = .Cells(2 + LigSource, ColSource).Value: .Cells(2 + LigSource, 14 + ColSource) = .Cells(2 + LigSource, ColSource).Value: .Cells(2 + LigSource, 21 + ColSource) = .Cells(2 + LigSource, ColSource).Value 'nom
.Cells(2 + LigSource, 28 + ColSource) = .Cells(2 + LigSource, ColSource).Value: .Cells(2 + LigSource, 35 + ColSource) = .Cells(2 + LigSource, ColSource).Value: .Cells(2 + LigSource, 42 + ColSource) = .Cells(2 + LigSource, ColSource).Value 'nom
.Cells(2 + LigSource, 49 + ColSource) = .Cells(2 + LigSource, ColSource).Value: .Cells(2 + LigSource, 56 + ColSource) = .Cells(2 + LigSource, ColSource).Value: .Cells(2 + LigSource, 63 + ColSource) = .Cells(2 + LigSource, ColSource).Value 'nom
.Cells(2 + LigSource, 70 + ColSource) = .Cells(2 + LigSource, ColSource).Value: .Cells(2 + LigSource, 77 + ColSource) = .Cells(2 + LigSource, ColSource).Value: .Cells(2 + LigSource, 84 + ColSource) = .Cells(2 + LigSource, ColSource).Value 'nom
.Cells(2 + LigSource, 91 + ColSource) = .Cells(2 + LigSource, ColSource).Value: .Cells(2 + LigSource, 98 + ColSource) = .Cells(2 + LigSource, ColSource).Value: .Cells(2 + LigSource, 105 + ColSource) = .Cells(2 + LigSource, ColSource).Value 'nom
.Cells(2 + LigSource, 112 + ColSource) = .Cells(2 + LigSource, ColSource).Value: .Cells(2 + LigSource, 119 + ColSource) = .Cells(2 + LigSource, ColSource).Value: .Cells(2 + LigSource, 126 + ColSource) = .Cells(2 + LigSource, ColSource).Value 'nom
.Cells(2 + LigSource, 133 + ColSource) = .Cells(2 + LigSource, ColSource).Value
'Début AM
.Cells(3 + LigSource, 7 + ColSource) = .Cells(3 + LigSource, ColSource).Value: .Cells(3 + LigSource, 14 + ColSource) = .Cells(3 + LigSource, ColSource).Value: .Cells(3 + LigSource, 21 + ColSource) = .Cells(3 + LigSource, ColSource).Value 'nom
.Cells(3 + LigSource, 28 + ColSource) = .Cells(3 + LigSource, ColSource).Value: .Cells(3 + LigSource, 35 + ColSource) = .Cells(3 + LigSource, ColSource).Value: .Cells(3 + LigSource, 42 + ColSource) = .Cells(3 + LigSource, ColSource).Value 'nom
.Cells(3 + LigSource, 49 + ColSource) = .Cells(3 + LigSource, ColSource).Value: .Cells(3 + LigSource, 56 + ColSource) = .Cells(3 + LigSource, ColSource).Value: .Cells(3 + LigSource, 63 + ColSource) = .Cells(3 + LigSource, ColSource).Value 'nom
.Cells(3 + LigSource, 70 + ColSource) = .Cells(3 + LigSource, ColSource).Value: .Cells(3 + LigSource, 77 + ColSource) = .Cells(3 + LigSource, ColSource).Value: .Cells(3 + LigSource, 84 + ColSource) = .Cells(3 + LigSource, ColSource).Value 'nom
.Cells(3 + LigSource, 91 + ColSource) = .Cells(3 + LigSource, ColSource).Value: .Cells(3 + LigSource, 98 + ColSource) = .Cells(3 + LigSource, ColSource).Value: .Cells(3 + LigSource, 105 + ColSource) = .Cells(3 + LigSource, ColSource).Value 'nom
.Cells(3 + LigSource, 112 + ColSource) = .Cells(3 + LigSource, ColSource).Value: .Cells(3 + LigSource, 119 + ColSource) = .Cells(3 + LigSource, ColSource).Value: .Cells(3 + LigSource, 126 + ColSource) = .Cells(3 + LigSource, ColSource).Value 'nom
.Cells(3 + LigSource, 133 + ColSource) = .Cells(3 + LigSource, ColSource).Value
'Fin AM
.Cells(4 + LigSource, 7 + ColSource) = .Cells(4 + LigSource, ColSource).Value: .Cells(4 + LigSource, 14 + ColSource) = .Cells(4 + LigSource, ColSource).Value: .Cells(4 + LigSource, 21 + ColSource) = .Cells(4 + LigSource, ColSource).Value 'nom
.Cells(4 + LigSource, 28 + ColSource) = .Cells(4 + LigSource, ColSource).Value: .Cells(4 + LigSource, 35 + ColSource) = .Cells(4 + LigSource, ColSource).Value: .Cells(4 + LigSource, 42 + ColSource) = .Cells(4 + LigSource, ColSource).Value 'nom
.Cells(4 + LigSource, 49 + ColSource) = .Cells(4 + LigSource, ColSource).Value: .Cells(4 + LigSource, 56 + ColSource) = .Cells(4 + LigSource, ColSource).Value: .Cells(4 + LigSource, 63 + ColSource) = .Cells(4 + LigSource, ColSource).Value 'nom
.Cells(4 + LigSource, 70 + ColSource) = .Cells(4 + LigSource, ColSource).Value: .Cells(4 + LigSource, 77 + ColSource) = .Cells(4 + LigSource, ColSource).Value: .Cells(4 + LigSource, 84 + ColSource) = .Cells(4 + LigSource, ColSource).Value 'nom
.Cells(4 + LigSource, 91 + ColSource) = .Cells(4 + LigSource, ColSource).Value: .Cells(4 + LigSource, 98 + ColSource) = .Cells(4 + LigSource, ColSource).Value: .Cells(4 + LigSource, 105 + ColSource) = .Cells(4 + LigSource, ColSource).Value 'nom
.Cells(4 + LigSource, 112 + ColSource) = .Cells(4 + LigSource, ColSource).Value: .Cells(4 + LigSource, 119 + ColSource) = .Cells(4 + LigSource, ColSource).Value: .Cells(4 + LigSource, 126 + ColSource) = .Cells(4 + LigSource, ColSource).Value 'nom
.Cells(4 + LigSource, 133 + ColSource) = .Cells(4 + LigSource, ColSource).Value
'Absence PM
.Cells(5 + LigSource, 7 + ColSource) = .Cells(5 + LigSource, ColSource).Value: .Cells(5 + LigSource, 14 + ColSource) = .Cells(5 + LigSource, ColSource).Value: .Cells(5 + LigSource, 21 + ColSource) = .Cells(5 + LigSource, ColSource).Value 'nom
.Cells(5 + LigSource, 28 + ColSource) = .Cells(5 + LigSource, ColSource).Value: .Cells(5 + LigSource, 35 + ColSource) = .Cells(5 + LigSource, ColSource).Value: .Cells(5 + LigSource, 42 + ColSource) = .Cells(5 + LigSource, ColSource).Value 'nom
.Cells(5 + LigSource, 49 + ColSource) = .Cells(5 + LigSource, ColSource).Value: .Cells(5 + LigSource, 56 + ColSource) = .Cells(5 + LigSource, ColSource).Value: .Cells(5 + LigSource, 63 + ColSource) = .Cells(5 + LigSource, ColSource).Value 'nom
.Cells(5 + LigSource, 70 + ColSource) = .Cells(5 + LigSource, ColSource).Value: .Cells(5 + LigSource, 77 + ColSource) = .Cells(5 + LigSource, ColSource).Value: .Cells(5 + LigSource, 84 + ColSource) = .Cells(5 + LigSource, ColSource).Value 'nom
.Cells(5 + LigSource, 91 + ColSource) = .Cells(5 + LigSource, ColSource).Value: .Cells(5 + LigSource, 98 + ColSource) = .Cells(5 + LigSource, ColSource).Value: .Cells(5 + LigSource, 105 + ColSource) = .Cells(5 + LigSource, ColSource).Value 'nom
.Cells(5 + LigSource, 112 + ColSource) = .Cells(5 + LigSource, ColSource).Value: .Cells(5 + LigSource, 119 + ColSource) = .Cells(5 + LigSource, ColSource).Value: .Cells(5 + LigSource, 126 + ColSource) = .Cells(5 + LigSource, ColSource).Value 'nom
.Cells(5 + LigSource, 133 + ColSource) = .Cells(5 + LigSource, ColSource).Value
'Début PM
.Cells(6 + LigSource, 7 + ColSource) = .Cells(6 + LigSource, ColSource).Value: .Cells(6 + LigSource, 14 + ColSource) = .Cells(6 + LigSource, ColSource).Value: .Cells(6 + LigSource, 21 + ColSource) = .Cells(6 + LigSource, ColSource).Value 'nom
.Cells(6 + LigSource, 28 + ColSource) = .Cells(6 + LigSource, ColSource).Value: .Cells(6 + LigSource, 35 + ColSource) = .Cells(6 + LigSource, ColSource).Value: .Cells(6 + LigSource, 42 + ColSource) = .Cells(6 + LigSource, ColSource).Value 'nom
.Cells(6 + LigSource, 49 + ColSource) = .Cells(6 + LigSource, ColSource).Value: .Cells(6 + LigSource, 56 + ColSource) = .Cells(6 + LigSource, ColSource).Value: .Cells(6 + LigSource, 63 + ColSource) = .Cells(6 + LigSource, ColSource).Value 'nom
.Cells(6 + LigSource, 70 + ColSource) = .Cells(6 + LigSource, ColSource).Value: .Cells(6 + LigSource, 77 + ColSource) = .Cells(6 + LigSource, ColSource).Value: .Cells(6 + LigSource, 84 + ColSource) = .Cells(6 + LigSource, ColSource).Value 'nom
.Cells(6 + LigSource, 91 + ColSource) = .Cells(6 + LigSource, ColSource).Value: .Cells(6 + LigSource, 98 + ColSource) = .Cells(6 + LigSource, ColSource).Value: .Cells(6 + LigSource, 105 + ColSource) = .Cells(6 + LigSource, ColSource).Value 'nom
.Cells(6 + LigSource, 112 + ColSource) = .Cells(6 + LigSource, ColSource).Value: .Cells(6 + LigSource, 119 + ColSource) = .Cells(6 + LigSource, ColSource).Value: .Cells(6 + LigSource, 126 + ColSource) = .Cells(6 + LigSource, ColSource).Value 'nom
.Cells(6 + LigSource, 133 + ColSource) = .Cells(6 + LigSource, ColSource).Value
'Fin PM
.Cells(7 + LigSource, 7 + ColSource) = .Cells(7 + LigSource, ColSource).Value: .Cells(7 + LigSource, 14 + ColSource) = .Cells(7 + LigSource, ColSource).Value: .Cells(7 + LigSource, 21 + ColSource) = .Cells(7 + LigSource, ColSource).Value 'nom
.Cells(7 + LigSource, 28 + ColSource) = .Cells(7 + LigSource, ColSource).Value: .Cells(7 + LigSource, 35 + ColSource) = .Cells(7 + LigSource, ColSource).Value: .Cells(7 + LigSource, 42 + ColSource) = .Cells(7 + LigSource, ColSource).Value 'nom
.Cells(7 + LigSource, 49 + ColSource) = .Cells(7 + LigSource, ColSource).Value: .Cells(7 + LigSource, 56 + ColSource) = .Cells(7 + LigSource, ColSource).Value: .Cells(7 + LigSource, 63 + ColSource) = .Cells(7 + LigSource, ColSource).Value 'nom
.Cells(7 + LigSource, 70 + ColSource) = .Cells(7 + LigSource, ColSource).Value: .Cells(7 + LigSource, 77 + ColSource) = .Cells(7 + LigSource, ColSource).Value: .Cells(7 + LigSource, 84 + ColSource) = .Cells(7 + LigSource, ColSource).Value 'nom
.Cells(7 + LigSource, 91 + ColSource) = .Cells(7 + LigSource, ColSource).Value: .Cells(7 + LigSource, 98 + ColSource) = .Cells(7 + LigSource, ColSource).Value: .Cells(7 + LigSource, 105 + ColSource) = .Cells(7 + LigSource, ColSource).Value 'nom
.Cells(7 + LigSource, 112 + ColSource) = .Cells(7 + LigSource, ColSource).Value: .Cells(7 + LigSource, 119 + ColSource) = .Cells(7 + LigSource, ColSource).Value: .Cells(7 + LigSource, 126 + ColSource) = .Cells(7 + LigSource, ColSource).Value 'nom
.Cells(7 + LigSource, 133 + ColSource) = .Cells(7 + LigSource, ColSource).Value
End With
End Sub
Sub DupliquerPlanningFixe()
'
Dim c As Range
Dim RangDuJour As Integer
Set c = Sheets("archives").Range("3:98").Find(what:=UsfEffectif.TxtNom.Value, lookat:=xlWhole) 'UsfEffectif.TxtNom.Value
If Not c Is Nothing Then
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
For RangDuJour = 1 To 6
DupliquerPlanningFix RangDuJour
Next RangDuJour
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Else
'MsgBox "La saisie du Planning peut-être anticipée au maximum 6 mois après la date d'aujourd'hui, Merci!!!"
End If
End Sub