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
| Option Base 1
Sub Export()
Dim ShExport As Worksheet
Dim ShSource As Worksheet
Dim TabCrit()
TabCrit = Array("13C*", "PSH*", "HSF*", "PCO*")
Set ShSource = ThisWorkbook.Worksheets("Feuil1")
' préparation de la feuille d'exportation
On Error Resume Next
Set ShExport = ThisWorkbook.Worksheets("Export")
ShExport.Cells.Delete
On Error GoTo 0
If ShExport Is Nothing Then Set ShExport = ThisWorkbook.Worksheets.Add: ShExport.Name = "Export"
' écriture des en-têtes de la BDD
With ShSource
.Cells(1, 1).Resize(1, .UsedRange.Columns.Count).Copy ShExport.Cells(3 + UBound(TabCrit), 1)
End With
With ShExport
' en-tête du critère
.Cells(1, 1).Value = ShSource.Cells(1, 9).Value
' les critères
.Cells(2, 1).Resize(UBound(TabCrit), 1).Value = Application.Transpose(TabCrit)
' le filtre avancé
ShSource.Cells(1, 1).CurrentRegion.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=.Cells(1, 1).Resize(UBound(TabCrit) + 1, 1), _
CopyToRange:=.Cells(3 + UBound(TabCrit), 1).Resize(1, .UsedRange.Columns.Count), _
Unique:=False
End With
End Sub |
Partager