AW: Autofilter aktualisieren
08.09.2015 14:28:15
fcs
Hallo Brandt,
Es ist einfacher, das Worksheet_Change_Ereignis in Tabelle1 auszuwerten.
Gruß
Franz
'Code unter dem Modul von Tabelle1
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wksF As Worksheet
Set wksF = ActiveWorkbook.Worksheets("Tabelle2")
Select Case Target.Address(False, False, xlA1)
Case "A1", "B1", "C1"
'Auto filter in Tabelle2 aktualisieren
With wksF
If .FilterMode = True Then .ShowAllData
With .AutoFilter.Range
If IsEmpty(Me.Range("A1")) Then
.AutoFilter Field:=1
Else
.AutoFilter Field:=1, Criteria1:=Me.Range("A1").Value
End If
If IsEmpty(Me.Range("B1")) Then
.AutoFilter Field:=2
Else
.AutoFilter Field:=2, Criteria1:=Me.Range("B1").Value
End If
If IsEmpty(Me.Range("C1")) Then
.AutoFilter Field:=3
Else
.AutoFilter Field:=3, Criteria1:=Me.Range("C1").Value
End If
End With
End With
Case Else
End Select
End Sub
oder wenn der Filter in einer Tabelle/Listobject gesetzut werden soll
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wksF As Worksheet
Set wksF = ActiveWorkbook.Worksheets("Tabelle2")
Select Case Target.Address(False, False, xlA1)
Case "A1", "B1", "C1"
'Auto Filter in Tabelle/Listobject aktualisieren
With wksF
With .ListObjects(1)
If .AutoFilter.FilterMode = True Then .AutoFilter.ShowAllData
With .AutoFilter.Range
If IsEmpty(Me.Range("A1")) Then
.AutoFilter Field:=1
Else
.AutoFilter Field:=1, Criteria1:=Me.Range("A1").Value
End If
If IsEmpty(Me.Range("B1")) Then
.AutoFilter Field:=2
Else
.AutoFilter Field:=2, Criteria1:=Me.Range("B1").Value
End If
If IsEmpty(Me.Range("C1")) Then
.AutoFilter Field:=3
Else
.AutoFilter Field:=3, Criteria1:=Me.Range("C1").Value
End If
End With
End With
End With
Case Else
End Select
End Sub