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
| Sub test()
Dim Dico As Object
Dim R As Range
Set Dico = CreateObject("Scripting.dictionary")
For i = 1 To 3
Set R = Sheets(i).UsedRange
Ditionaire R, 2, 1, Dico
Next
i = Dico.items
For n = 0 To Dico.Count - 1
CreerClasseur ThisWorkbook, "C:\MyTest", i(n)
Next
End Sub
Sub Ditionaire(R As Range, lStart As Long, C As Integer, Dico As Object)
Dim L As Long
For L = lStart To R.Rows.Count
If Not Dico.Exists(R(L, C).Value) Then Dico.Add R(L, C).Value, R(L, C).Value
Next
End Sub
Sub CreerClasseur(Source As Workbook, Chemin, filiales)
If Right(Chemin, 1) <> "\" Then Chemin = Chemin & "\"
Dim Fltr As Workbook
Dim NewWb As Workbook
Set Fltr = Workbooks.Add
Fltr.Sheets(1).Range("A1") = "filiales"
Fltr.Sheets(1).Range("A2") = filiales
Set NewWb = Workbooks.Add
For i = 1 To 3
NewWb.Sheets(i).Name = Source.Sheets(i).Name
FiltreActif Source.Sheets(i).UsedRange, Fltr.Sheets(1).UsedRange, NewWb.Sheets(i).Range("A1"), False
Next
Fltr.Close False
NewWb.SaveAs Chemin & filiales & ".xlsx"
NewWb.Close False
Set NewWb = Nothing
Set Fltr = Nothing
End Sub
Function FiltreActif(RangeSource As Range, CriterRange As Range, CopyRange As Range, Optional Unique As Boolean = True) As Boolean
FiltreActif = False
On Error Resume Next
RangeSource.AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=CriterRange _
, CopyToRange:=CopyRange, Unique:=Unique
DoEvents
If Err = 0 Then FiltreActif = True
On Error GoTo 0
End Function |
Partager