|First, a posting tip:|
Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read.
As for your question, my first thought was to use a Helper Column in your sheet and then filter on the Helper Column.
e.g. Put =MONTH(M6) in an empty column and drag it down. I used 12, but it doesn't matter. You can even hide the column if you don't want to see it.
Then your code would look like this:
ActiveSheet.Range("$A$6:$AQ$1000").AutoFilter Field:=12, Criteria1:=Month(Date)
Since the Helper Column would always contain the current month number for Dates within the current Month, those numbers would be a match for the result of Month(Date) within VBA.
Then I thought about it for a second longer and decided that you can't be the first one to want to filter on the current month. I used my excellent Googling skills and found the following at:
ActiveSheet.Range("$A$6:$AQ$1000").AutoFilter Field:=13, _
Criteria1:=">=" & DateSerial(Year(Date), Month(Date), 1), _
Criteria2:="<=" & DateSerial(Year(Date), Month(Date) + 1, 1) - 1
There's no need for a Helper Column when that method is used. What is it doing is looking for dates that are >= to first of this month and <= the day before the first day of next month. In other words, any date within the current month.
Assuming you choose the VBA-only method, please keep the concept of a Helper Column in mind. There are many times when a intermediate step is required to get to the end result. This applies both within Excel and also when using VBA. That intermediate step can be placed in a Helper Column and referred to for further calculations.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.