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
| Private Sub Worksheet_Activate()
Dim LastLig As Long, NewLig As Long
Dim LastCol As Integer, i As Integer
Dim Ws As Worksheet
Application.ScreenUpdating = False
With Worksheets("Synthese")
LastLig = .Cells(.Rows.Count, "A").End(xlUp).Row
If LastLig > 1 Then .Rows(2 & ":" & LastLig).Clear
NewLig = 2
For Each Ws In ThisWorkbook.Worksheets
If InStr("Synthese|Feuil24|Feuil15|", Ws.Name & "|") = 0 Then ' entre "" mettre le nom de toutes les feuilles à exclure séparés d'un |
With Ws
LastLig = .Cells(.Rows.Count, 1).End(xlUp).Row
If LastLig > 1 Then
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = 1 To LastCol
Call Transfert(.Name, i, NewLig)
Next i
NewLig = NewLig + LastLig - 1
End If
End With
End If
Next Ws
End With
End Sub
'Procédure qui permet de tansferer chaque colonne vers la colonne dont le titre correspond dans la feuille synthèse
'Si le titre n'existe pas dans synthèse, elle le crée à la fin des colonne et y transfert les données
Private Sub Transfert(ByVal ShName As String, ByVal Col As Integer, ByVal NewLig As Long)
Dim c As Range
Dim Titre As String
Dim LaCol As Integer
Dim Ws As Worksheet
Dim LastLig As Long
With Worksheets(ShName)
Titre = .Cells(1, Col).Value
If Titre <> "" Then
Set Ws = Worksheets("Synthese")
Set c = Ws.Rows(1).Find(Titre, LookIn:=xlValues, Lookat:=xlWhole)
If Not c Is Nothing Then
LaCol = c.Column
Set c = Nothing
Else
LaCol = Ws.Cells(1, Ws.Columns.Count).End(xlToLeft).Column + 1
Ws.Cells(1, LaCol).Value = Titre
End If
LastLig = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range(.Cells(2, Col), .Cells(LastLig, Col)).Copy Ws.Cells(NewLig, LaCol)
Set Ws = Nothing
End If
End With
End Sub |
Partager