Private Sub Worksheet_Activate()
Dim i&, ncol%
Application.ScreenUpdating = False
If FilterMode Then ShowAllData 'si la feuille est filtrée
Cells.Delete 'RAZ
With Sheets(1) '1ère feuille, à adapter éventuellement
.[A1].CurrentRegion.Copy [A1] 'copier-coller
Rows(1).RowHeight = .Rows(1).RowHeight 'copie la hauteur
Rows(2).RowHeight = .Rows(2).RowHeight 'copie la hauteur
End With
With [A1].CurrentRegion
ncol = .Columns.Count
If ncol > 3 Then
If .Rows.Count > 1 Then .Offset(1).Resize(.Rows.Count - 1).Sort .Columns(1), xlAscending, .Columns(2), , xlAscending, Header:=xlYes 'tri sur 2 colonnes
For i = .Rows.Count To 3 Step -1
If .Cells(i, 1) <> .Cells(i - 1, 1) And .Cells(i, 2) <> .Cells(i - 1, 2) Then
.Rows(i).EntireRow.Insert 'insère une ligne
.Cells(i, 4) = .Cells(i + 1, 1) & " " & .Cells(i + 1, 2) & " " & Format(.Cells(i + 1, 3), "dd/mm/yyyy")
'---mises en forme---
With .Cells(i, 4).Resize(, ncol - 3)
.Merge 'fusionne
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Bold = True
.Interior.Color = RGB(146, 208, 80) 'vert
.RowHeight = 25
End With
End If
Next
End If
End With
Columns("A:C").Delete 'supprime les 3 colonnes
Columns.AutoFit 'ajuste les largeurs
End Sub