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
| Option Explicit
Sub Tst()
Dim Ws As Worksheet
Dim i As Long, j As Long
Dim Cel As Range
Dim LastRow As Long
Application.ScreenUpdating = False
Workbooks.Add
Range("A1") = "Nom"
Range("B1") = "Numero"
i = 2: j = 2
For Each Ws In ThisWorkbook.Worksheets
'LastRow = Split(Ws.UsedRange.Address, "$")(4)
LastRow = Ws.Cells.SpecialCells(xlCellTypeLastCell).Row
For Each Cel In Ws.Range("A1:IV1")
Select Case UCase(Cel.Text)
Case "NOM"
Ws.Range(Cel.Offset(1, 0), Cel.Offset(LastRow - 1, 0)).Copy Range("A" & i)
i = i + LastRow - 1
Case "NUMERO"
Ws.Range(Cel.Offset(1, 0), Cel.Offset(LastRow - 1, 0)).Copy Range("B" & j)
j = j + LastRow - 1
End Select
Next Cel
Next Ws
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
For j = LastRow To 2 Step -1
If Application.WorksheetFunction.CountBlank(Range(Cells(j, 1), Cells(j, 2))) = 2 Then
Rows(j).Delete Shift:=xlUp
End If
Next j
Application.DisplayAlerts = False
ChDir ThisWorkbook.Path
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & "Recap.csv", FileFormat:=xlCSV, local:=True
ActiveWindow.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub |
Partager