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
| Sub CreerFeuillesOccurrenceUnique()
Dim wsSource As Worksheet
Dim wsDest
Dim rngSource As Range
Dim cell As Range
Dim dict As Object
Dim id As String
Dim i As Integer, LastEmpty As Long
Dim compteur As Integer
' Initialiser le compteur
compteur = 1
Set wsSource = ThisWorkbook.Sheets("Feuil1")
LastEmpty = wsSource.Cells(wsSource.Rows.Count, "N").End(xlUp).Row
Set rngSource = wsSource.Range("N1:N" & LastEmpty)
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare
' Créer une feuille pour chaque occurrence unique
For i = 1 To LastEmpty
id = wsSource.Range("N" & i).Value
If Not dict.exists(id) And id <> "" Then
Set wsDest = ThisWorkbook.Sheets.Add(After:=Sheets(Sheets.Count))
wsDest.Name = Left(id, 10) & compteur
compteur = compteur + 1
dict.Add id, wsDest
End If
Next
Dim u
u = dict.Items
' Copier le tableau dans les nouvelles feuilles
For i = 0 To dict.Count - 1
Set wsDest = u(i)
wsSource.UsedRange.Copy Destination:=wsDest.Range("A1")
Next i
End Sub |
Partager