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
|
Sub ZOOM()
Dim WsSource As Worksheet, filtre As Workbook, WsCible1 As Worksheet, WsCible2 As Worksheet
Dim xlBook As Workbook
Set WsSource = ThisWorkbook.Sheets("Final")
On Error Resume Next: WsSource.ShowAllData: On Error GoTo 0
ListeParam = Array("AUTO", "DAB CORPORATE", "DAB DOMESTIQUE", "DC", "NR", "RC", "TRAN")
For i = LBound(ListeParam) To UBound(ListeParam)
Set filtre = Workbooks.Add
filtre.Sheets(1).Range("A1") = "TRAITE"
filtre.Sheets(1).Range("B1") = "DIRECT/EDW"
filtre.Sheets(1).Range("A2") = "" & ListeParam(i)
filtre.Sheets(1).Range("B2") = "DIRECT"
filtre.Sheets(2).Range("A1") = "TRAITE"
filtre.Sheets(2).Range("B1") = "DIRECT/EDW"
filtre.Sheets(2).Range("A2") = "" & ListeParam(i)
filtre.Sheets(2).Range("B2") = "EDW"
ChDir "P:\ZOOM"
Application.ScreenUpdating = False
Set xlBook = Excel.Application.Workbooks.Add
'xlBook.SaveAs ("ZOOM " & ListeParam(i) & ".xls")
Set WsCible1 = xlBook.Sheets(1)
WsCible1.Name = "DIRECT"
Set WsCible2 = xlBook.Sheets(2)
WsCible2.Name = "EDW"
FiltreActif WsSource.UsedRange, filtre.Sheets(1).UsedRange, WsCible1.Range("A1")
FiltreActif WsSource.UsedRange, filtre.Sheets(2).UsedRange, WsCible2.Range("A1")
xlBook.Close True, "ZOOM " & ListeParam(i)
Application.ScreenUpdating = True
filtre.Close False
Set filtre = Nothing
Set xlBook = Nothing
Next i
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
'MsgBox Err.Description
On Error GoTo 0
End Function |
Partager