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
| Sub ron13()
Dim oSh1 As Excel.Worksheet, oSh2 As Excel.Worksheet, oRng As Excel.Range
Dim iPrem As Long, iDer As Long, jPrem As Integer, jDer As Integer
Dim vTab As Variant, vCol As Variant, i As Long, k As Long, j As Integer, jCompt As Integer, jCol As Integer
Const sTitre = "tutu"
Set oSh1 = ThisWorkbook.Worksheets("Feuil1")
Set oSh2 = ThisWorkbook.Worksheets("Feuil2")
oSh2.UsedRange.ClearContents
Set oRng = oSh1.UsedRange
iPrem = oRng.Row
iDer = iPrem + oRng.Rows.Count - 1
jPrem = oRng.Column
jDer = jPrem + oRng.Columns.Count
vTab = oRng.Value
jCompt = 1
For i = 1 To UBound(vTab, 1)
For j = 1 To UBound(vTab, 2)
If vTab(i, j) = sTitre Then
jCol = jPrem + j - 1
Set oRng = oSh2.Range(oSh2.Cells(iPrem, jCompt), oSh2.Cells(iDer, jCompt))
vCol = oRng.Value
For k = 1 To UBound(vTab, 1)
vCol(k, 1) = vTab(k, j)
Next k
oRng.Value = vCol
jCompt = jCompt + 1
End If
Next j
Next i
Set oSh1 = Nothing
Set oSh2 = Nothing
Set oRng = Nothing
vTab = Empty
vCol = Empty
End Sub |
Partager