Sub classer() 'À placer dans un module standard.
Dim iFl&, jFl&
Dim oFl(), cFl&, lFl&, nCl&, dFl$
With Application: .ScreenUpdating = 0: .Calculation = -4135: .EnableEvents = 0: End With
'Paramètres :
oFl = Array("1", "2", "3") 'feuilles d'origine.
dFl = "classement" 'feuille de destination.
cFl = 4 'colonnes à traiter dans chaqu feuille.
lFl = 16 'lignes à copier dans chaque feuille.
nCl = 8 'colonnes à conserver.
Worksheets.Add
On Error GoTo E
For iFl = 0 To UBound(oFl)
With Sheets(oFl(iFl))
For jFl = 1 To cFl
.Cells(1, 2 * jFl).Resize(lFl, 1).Copy Destination:=Cells(1, cFl * iFl + jFl)
Next
End With
Next
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A1").Resize(1, cFl * (UBound(oFl) + 1)), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A1").Resize(lFl, cFl * (UBound(oFl) + 1))
.Header = xlNo
.MatchCase = False
.Orientation = xlLeftToRight
.Apply
End With
'====== Sortie (valeurs seules) : ======
ReDim oVal(1 To lFl, 1 To nCl)
oVal = Range("A1").Resize(lFl, nCl).Value
Worksheets(dFl).Cells(2, 2).Resize(lFl, nCl).Value = oVal
'====== ou (formules, formats) : =======
' Range("A1").Resize(lFl, nCl).Copy Destination:=Worksheets(dFl).Cells(2, 2)
'===========================================
R: Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
On Error Resume Next
Sheets(dFl).Activate
With Application: .EnableEvents = 1: .Calculation = -4105: .ScreenUpdating = 1: End With
Exit Sub
E:
MsgBox "Une erreur est survenue." & vbLf & vbLf & _
"Vérifiez qu 'il existe des données à traiter" & vbLf & _
"et que toutes les feuilles requises existent.", vbOKOnly
Resume R
End Sub