I have a spreadsheet and it links to another workbook. The issue is when the filter is on it does not change as new data is added; you have to refilter the data.
I know there is a way to have it autofilter as data appears using a Macro. I have been trying to figure the Macro out for 5 hours now and can not get it right. The closest I've gotten is I set a filter and then when new data appears, it erases the filter and adds all data together. I need the filter to stay!
Private Sub Worksheet_Change(ByVal Target As Range)
If Me.FilterMode = True Then
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
With ActiveWorkbook
.CustomViews.Add ViewName:="B2", RowColSettings:=True
Me.AutoFilterMode = False
.CustomViews("B2").Show
.CustomViews("B2").Delete
End With
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End If
End Sub