Variante 2
25.01.2015 17:02:08
Tino
Hallo,
vielleicht geht es so?!
Sub FilternKopieren()
Dim rngHelp As Range, rngUsedRange As Range
Dim NewWB As Workbook
Dim i%, ii%
Dim lngSuchSpalte&
Dim varFormel
Dim ArSuche
'Filter OR = Oder; AND = Und, Trennzeichen in Or/And = , (Komma)
ArSuche = Array("OR(A,E)", "P", "S")
'Suchspalte angeben 1 = Spalte A; 2 = Spalte B usw...
lngSuchSpalte = 1
On Error GoTo ErrorHandler:
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Tabelle1 'Tabelle anpassen <<<<<<<<<<<<
Set rngUsedRange = .UsedRange
Set rngHelp = .UsedRange.Columns(.UsedRange.Columns.Count).Cells(1, 1)
Set rngHelp = rngHelp.Offset(, 1).Resize(2)
End With
For i = Lbound(ArSuche) To Ubound(ArSuche)
If InStr(ArSuche(i), "(") > 0 Then
varFormel = Replace(ArSuche(i), "(", "(RC" & lngSuchSpalte & "=" & Chr(34))
varFormel = Replace(varFormel, ",", Chr(34) & ",RC" & lngSuchSpalte & "=" & Chr(34))
varFormel = Replace(varFormel, ")", Chr(34) & ")")
Else
varFormel = "RC" & lngSuchSpalte & "=" & Chr(34) & ArSuche(i) & Chr(34)
End If
rngHelp.Cells(2, 1).FormulaR1C1 = "=" & varFormel
Set NewWB = Workbooks.Add
With NewWB
For ii = .Worksheets.Count To 2 Step -1
Worksheets(ii).Delete
Next ii
With .Worksheets(ii)
rngUsedRange.AdvancedFilter xlFilterCopy, rngHelp, .Cells(1, 1), False
End With
End With
Next i
ErrorHandler:
If Not rngHelp Is Nothing Then rngHelp.EntireColumn.Delete
Application.ScreenUpdating = True
Application.DisplayAlerts = True
If Err.Number <> 0 Then
MsgBox Err.Description, _
vbCritical + vbMsgBoxSetForeground + vbMsgBoxHelpButton, _
"Error: " & Err.Number, Err.HelpFile, Err.HelpContext
End If
End Sub
Gruß Tino