1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105
|
Sub RemplirLOngletStage()
Dim J As Long
Dim Continuer As Boolean
Dim ShOnglets As Worksheet
Dim PremiereLigneOnglets As Long
Dim DerniereLigneOnglets As Long
Dim AireOnglets As Range
Dim CelluleOnglets As Range
Dim ShStagiaire As Worksheet
Dim ShStages As Worksheet
Set ShStages = Sheets("STAGE")
With ShStages
.Range(.Cells(2, 1), .Cells(.Rows.Count, .Columns.Count)).Clear
End With
Set ShOnglets = Sheets("Liste des stagiaires") ' A modifier, c'est l'onglet où s'applique la variable k dans votre code
With ShOnglets
' Set AireOnglets = .Range(.Cells(5, 2), .Cells(7, 2)) ' 7 doit sans doute être modifié,
' il vaudrait mieux trouver automatiquement la dernière ligne
PremiereLigneOnglets = 5
DerniereLigneOnglets = .Cells(.Rows.Count, 2).End(xlUp).Row
If DerniereLigneOnglets < PremiereLigneOnglets Then
MsgBox "Aucun onglet stagiaire, fin de programme", vbCritical, "Contrôle présence onglets stagiaires dans liste des onglets"
Exit Sub
End If
Set AireOnglets = .Range(.Cells(5, 2), .Cells(DerniereLigneOnglets, 2))
For Each CelluleOnglets In AireOnglets
Continuer = False
Set ShStagiaire = Nothing
For J = 1 To Worksheets.Count
If Worksheets(J).Name = CelluleOnglets Then
Set ShStagiaire = Worksheets(J)
Continuer = True
Exit For
End If
Next J
If Continuer = True Then
RecenserLesStages ShStagiaire, ShStages
End If
Next CelluleOnglets
Set AireOnglets = Nothing
End With
With ShStages
.Activate
.Columns(2).HorizontalAlignment = xlCenter
End With
Set ShStagiaire = Nothing
Set ShOnglets = Nothing
Set ShStages = Nothing
End Sub
Sub RecenserLesStages(ByVal FeuilleStagiaire As Worksheet, ByVal FeuilleStages As Worksheet)
Dim AireStagesStagiaire As Range
Dim CelluleStagesStagiaire As Range
Dim DerniereLigneStagesStagiaire As Long
Dim DerniereLigneStages As Long
Dim NomDuStagiaire As String
With FeuilleStagiaire
NomDuStagiaire = .Cells(2, 4)
DerniereLigneStagesStagiaire = .Cells(35, 4).End(xlUp).Row
If DerniereLigneStagesStagiaire >= 24 Then
Set AireStagesStagiaire = .Range(.Cells(24, 4), .Cells(DerniereLigneStagesStagiaire, 4))
For Each CelluleStagesStagiaire In AireStagesStagiaire
If CelluleStagesStagiaire <> "" Then
With FeuilleStages
DerniereLigneStages = .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(DerniereLigneStages + 1, 1) = NomDuStagiaire
With .Cells(DerniereLigneStages + 1, 2)
.Value = CelluleStagesStagiaire
.NumberFormat = "dd/mm/yyyy"
End With
.Cells(DerniereLigneStages + 1, 3) = CelluleStagesStagiaire.Offset(0, 2)
End With
End If
Next CelluleStagesStagiaire
Set AireStagesStagiaire = Nothing
End If
End With
End Sub |
Partager