Hi, the below code filter data from the tRegistration sheet and show the selected data in a list box and Filtered Data sheet. If the data sheet is a range, the data sheet won't be affected by the code, but when I turned the data sheet into a table, the filter is activated.
Code:
Case "Business Address"
ActiveSheet.AutoFilterMode = False
ListBox1.Clear
ActiveSheet.Range("A1:X" & Sheets("tRegistration").Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=4, Criteria1:=TextBox25.Value & "*", Operator:=xlAnd
Sheets("FilteredData").Cells.Clear
If ActiveSheet.Range("A1").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count <= 1 Then
GoTo here2:
Else
ActiveSheet.Range("A2:X" & Sheets("tRegistration").Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("FilteredData").Range("A2")
End If
Sheets("FilteredData").Columns.AutoFit
ListBox1.List = Sheets("FilteredData").Range("A2:X" & Sheets("FilteredData").Cells(Rows.Count, 1).End(xlUp).Row).Value
here2:
ActiveSheet.AutoFilterMode = False
Call Clear
How to make the code show the filtered records only in the list box and Filtered Data Sheet? The data in the tRegistration should remain intact.
I tried this but the problem is still there.
Code:
Sub FilterOff()
If ActiveSheet.ListObjects(1).ShowAutoFilter Then
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Else
ActiveSheet.ListObjects(1).ShowAutoFilter = True
End If
End Sub
Thank you.