Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Not Sh.Name Like "Utilisateur*" Then Exit Sub
Dim nom$, lig&, w As Worksheet, h&, ncol%, r As Range, P As Range
Application.ScreenUpdating = False
Sh.Rows("2:" & Sh.Rows.Count).Clear 'RAZ
Sh.Columns.ColumnWidth = 10.71
nom = Mid(Sh.Name, 13)
lig = 3
For Each w In Worksheets
If w.Name Like "Données*" Then
h = Application.CountIf(w.Columns(1), nom) + 1
If h > 1 Then
Sh.Cells(lig, 1) = "Voici les " & LCase(w.Name) & " de la veille :"
With w.[A1].CurrentRegion
ncol = .Columns.Count - 1
.AutoFilter 1, nom 'filtre automatique
If ncol Then
Set r = Sh.Cells(lig + 1, 1).Resize(h)
.Columns(2).Resize(, ncol).Copy r
Set P = Union(IIf(P Is Nothing, r, P), r)
End If
End With
w.AutoFilterMode = False 'retire le filtre automatique
lig = lig + h + 2
End If
End If
Next w
'---largeur des colonnes---
Sh.Columns(2).Resize(, Sh.Columns.Count - 1).AutoFit
If Not P Is Nothing Then P.Columns.AutoFit
'---coloration en A1---
Sh.Rows(1).Interior.ColorIndex = xlNone
For ncol = 1 To Sh.Columns.Count
With Sh.Cells(1).Resize(, ncol)
If .Width > 100 Then .Interior.Color = RGB(146, 208, 80): Exit For 'largeur 100 à adapter
End With
Next ncol
With Sh.UsedRange: End With 'actualise les barres de défilement
End Sub