Sub ArchiverToutFixe() 'issu de UsfEffectif
Dim RangDuJour As Integer
If UsfEffectif.ObFixe = True Then
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
For RangDuJour = 1 To 6
ArchiverUnPlanningFixe RangDuJour
Next RangDuJour
Else
End If
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub
Sub ArchiverUnPlanningFixe(RangDuJour As Integer) 'issu de UsfEffectif
Dim LigCible As Long, ColCible As Long, MaDate As Long
Application.ScreenUpdating = False
derniereligne = Sheets("Archives").Range("A1").End(xlDown).Row + 1
LigCible = derniereligne
MaDate = CDate(UsfEffectif.TxtDateFixe)
ColCible = Application.Match(MaDate, Sheets("Archives").Range("2:2"), 0) + RangDuJour - 1
If RangDuJour = 1 Then
Sheets("Archives").Cells(LigCible, ColCible).Value = UsfEffectif.TxtNom.Value
Sheets("Archives").Cells(LigCible + 1, ColCible).Value = UsfEffectif.CbSemTypeFixe.Value
Sheets("Archives").Cells(LigCible + 2, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Lundi AM]"))
Sheets("Archives").Cells(LigCible + 3, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi AM Début]"))
Sheets("Archives").Cells(LigCible + 4, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi AM Fin]"))
Sheets("Archives").Cells(LigCible + 5, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Lundi PM]"))
Sheets("Archives").Cells(LigCible + 6, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi PM Début]"))
Sheets("Archives").Cells(LigCible + 7, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Lundi PM Fin]"))
End If
If RangDuJour = 2 Then
Sheets("Archives").Cells(LigCible, ColCible).Value = UsfEffectif.TxtNom.Value
Sheets("Archives").Cells(LigCible + 1, ColCible).Value = UsfEffectif.CbSemTypeFixe.Value
Sheets("Archives").Cells(LigCible + 2, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mardi AM]"))
Sheets("Archives").Cells(LigCible + 3, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi AM Début]"))
Sheets("Archives").Cells(LigCible + 4, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi AM Fin]"))
Sheets("Archives").Cells(LigCible + 5, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mardi PM]"))
Sheets("Archives").Cells(LigCible + 6, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi PM Début]"))
Sheets("Archives").Cells(LigCible + 7, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mardi PM Fin]"))
End If
If RangDuJour = 3 Then
Sheets("Archives").Cells(LigCible, ColCible).Value = UsfEffectif.TxtNom.Value
Sheets("Archives").Cells(LigCible + 1, ColCible).Value = UsfEffectif.CbSemTypeFixe.Value
Sheets("Archives").Cells(LigCible + 2, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mercredi AM]"))
Sheets("Archives").Cells(LigCible + 3, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi AM Début]"))
Sheets("Archives").Cells(LigCible + 4, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi AM Fin]"))
Sheets("Archives").Cells(LigCible + 5, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Mercredi PM]"))
Sheets("Archives").Cells(LigCible + 6, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi PM Début]"))
Sheets("Archives").Cells(LigCible + 7, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Mercredi PM Fin]"))
End If
If RangDuJour = 4 Then
Sheets("Archives").Cells(LigCible, ColCible).Value = UsfEffectif.TxtNom.Value
Sheets("Archives").Cells(LigCible + 1, ColCible).Value = UsfEffectif.CbSemTypeFixe.Value
Sheets("Archives").Cells(LigCible + 2, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Jeudi AM]"))
Sheets("Archives").Cells(LigCible + 3, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi AM Début]"))
Sheets("Archives").Cells(LigCible + 4, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi AM Fin]"))
Sheets("Archives").Cells(LigCible + 5, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Jeudi PM]"))
Sheets("Archives").Cells(LigCible + 6, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi PM Début]"))
Sheets("Archives").Cells(LigCible + 7, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Jeudi PM Fin]"))
End If
If RangDuJour = 5 Then
Sheets("Archives").Cells(LigCible, ColCible).Value = UsfEffectif.TxtNom.Value
Sheets("Archives").Cells(LigCible + 1, ColCible).Value = UsfEffectif.CbSemTypeFixe.Value
Sheets("Archives").Cells(LigCible + 2, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Vendredi AM]"))
Sheets("Archives").Cells(LigCible + 3, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi AM Début]"))
Sheets("Archives").Cells(LigCible + 4, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi AM Fin]"))
Sheets("Archives").Cells(LigCible + 5, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Vendredi PM]"))
Sheets("Archives").Cells(LigCible + 6, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi PM Début]"))
Sheets("Archives").Cells(LigCible + 7, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Vendredi PM Fin]"))
End If
If RangDuJour = 6 Then
Sheets("Archives").Cells(LigCible, ColCible).Value = UsfEffectif.TxtNom.Value
Sheets("Archives").Cells(LigCible + 1, ColCible).Value = UsfEffectif.CbSemTypeFixe.Value
Sheets("Archives").Cells(LigCible + 2, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Samedi AM]"))
Sheets("Archives").Cells(LigCible + 3, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi AM Début]"))
Sheets("Archives").Cells(LigCible + 4, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi AM Fin]"))
Sheets("Archives").Cells(LigCible + 5, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Absences Samedi PM]"))
Sheets("Archives").Cells(LigCible + 6, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi PM Début]"))
Sheets("Archives").Cells(LigCible + 7, ColCible).Value = Application.WorksheetFunction.XLookup(UsfEffectif.CbSemTypeFixe.Value, Range("TbSemType[Nom Sem Type]"), Range("TbSemType[Samedi PM Fin]"))
End If
End Sub