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 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
|
Sub Build_recap()
' Dev versuib, Without Error mgmt and clean comments !
Dim Wsh As Worksheet
Dim Data_exp() As Variant
Dim Data_rec As Integer, Data_ind As Integer
Dim Coln As Long, Rown As Long, MaxCol As Integer
Dim InpRng As Range
Dim FoundNam As Boolean
' Get the max records to proceed to set the dimension of Data_exp array
For Each Wsh In ThisWorkbook.Worksheets
If Wsh.UsedRange.Rows.Count > Data_rec And Wsh.Name <> "Recap" Then
Data_rec = Wsh.UsedRange.Rows.Count 'To be changed if it's not coherent
End If
Next Wsh
' Redim according to the number of worksheets and names
ReDim Data_exp(1 To Data_rec + 2, 1 To ThisWorkbook.Worksheets.Count + 3)
MaxCol = UBound(Data_exp, 2)
' Parse all the sheets except thee Recap and build an arrat
Data_rec = 0
For Each Wsh In ThisWorkbook.Worksheets
If Wsh.Name <> "Recap" Then
Set InpRng = Wsh.Range("A2").CurrentRegion
For Rown = 2 To InpRng.Rows.Count
FoundNam = False
For Data_ind = 1 To Data_rec
If Data_exp(Data_ind, 1) = InpRng(Rown, 1) And Data_exp(Data_ind, 2) = InpRng(Rown, 2) Then
FoundNam = True
Data_exp(Data_ind, MaxCol) = Data_exp(Data_ind, MaxCol) + 1
Data_exp(Data_ind, Data_exp(Data_ind, MaxCol) + 2) = Wsh.Name
End If
Next Data_ind
If FoundNam = False Then ' Record not found, create it in the array
Data_rec = Data_rec + 1
Data_exp(Data_rec, 1) = InpRng(Rown, 1)
Data_exp(Data_rec, 2) = InpRng(Rown, 2)
Data_exp(Data_ind, MaxCol) = Data_exp(Data_ind, MaxCol) + 1
Data_exp(Data_ind, Data_exp(Data_ind, MaxCol) + 2) = Wsh.Name
End If
Next Rown
End If
Next Wsh
'Report
Worksheets("Recap").Activate
Cells.Clear
For Rown = 1 To Data_rec
For Coln = 1 To MaxCol - 1
Cells(Rown, Coln) = Data_exp(Rown, Coln)
Next Coln
Next Rown
End Sub |
Partager