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
| Sub TestCopyRange_2()
' Feuilles du même classeur mais ListObject
Dim oListSource As ListObject
Dim rng As Range
Dim tbl()
Dim Elem As Integer
Dim addr As String
'
tbl = Array("Bruxelles", "Londres", "Athenes")
If Start(tbl) Then
'
For Elem = 0 To UBound(tbl)
Set oListSource = ThisWorkbook.Worksheets(tbl(Elem)).ListObjects(1)
' Importation
If Elem = UBound(tbl) Then
Set rng = mStdCopyRange.CopyRange(oListSource, shtTarget, ClearSheet:=Elem = 0, AddLabel:=oListSource.Parent.Name)
Else
CopyRange oListSource, shtTarget, ClearSheet:=Elem = 0, AddLabel:=oListSource.Parent.Name
End If
Next
shtTarget.Cells.EntireColumn.AutoFit
'TRI Macro
ActiveWorkbook.Worksheets("tbl").ListObjects(1).Sort. _
SortFields.Add2 Key:=Range("ListObjects(1)[IdClient]"), SortOn:= _
xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("tbl").ListObjects("ListObjects(1)"). _
Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
MsgBox rng.Address(external:=True)
End If
' End of process
Set rng = Nothing: Set oListSource = Nothing
End Sub |
Partager