I've got a spreadsheet with a main table of data on the first tab. In the main table is a column for "renewal date". I am trying to do two things:
1. create an advanced filter to copy any record from the main table to a second tab if the record has a January renewal date. I would like it to copy any record with a January date, regardless of year, so it could be January 2013, 2015 or 2020.
2. I would like the filter to update automatically as new data is entered into the main table.
I created a dynamic named range called SalesTable so the target range of data on the main tab would update automatically. I then used that named range in the advanced filter "list range" field.
In the attached spreadsheet you can see that I am using criteria for my advanced filter of >=1/1/2016 and <=1/1/2016 just because I wasn't sure how to make it only filter based on month. As you can see in the January tab, the advanced filter correctly pulled out January 2016 dates. I just need to know how to make it look at the month for the criteria.
And then to make the filter update automatically, I added the following code to the January tab so it would refresh. For whatever reason it isn't refreshing.
Private Sub Worksheet_Change(ByVal Target As Range)
If Me.FilterMode = True Then
.EnableEvents = False
.ScreenUpdating = False
.CustomViews.Add ViewName:="Mine", RowColSettings:=True
Me.AutoFilterMode = False
.EnableEvents = True
.ScreenUpdating = True