VBA TO EXCLUDE CURRENT MONTH

[aaronprudence]

Hi,

Very new to VBA and struggling. Any help would be very much appreciated. Thank you.

I have a workbook with several worksheets that I want to filter all at once to exclude the current month.
Data is in tables with date in Column Q.
Date is in dd/mm/yyyy format

I am getting a "AutoFilter method of Range Class failed" error

Sub Exclude_Current_Month_Filter()
    Dim ws As Worksheet, d1 As Long, d2 As Long
    d1 = Date - Day(Date) 'End of last month
    d2 = DateSerial(Year(Date), Month(Date) + 1, 1) 'Begining of next month
    For Each ws In Worksheets
        If ws.ListObjects.Count > 0 Then
            ws.ListObjects(1).Range.AutoFilter 'clear previous filer if any
            ws.ListObjects(1).Range.AutoFilter 17, "<=" & d1, xlOr, ">=" & d2 'Filter exclude current month column Q
        End If
    Next
End Sub

 

[mart37]

The VBA looks fine. Can you check on wich sheet the error occurs? Have that sheet a different table?

 

[aaronprudence]

Thanks for your reply.

How do I find out the exact sheet which the error occurs?
I have 14 sheets in total and the filter needs to apply to sheets 3 to 10.

I run the debug and this is the line it flags.

ws.ListObjects(1).Range.AutoFilter 17, "<=" & d1, xlOr, ">=" & d2 'Filter exclude current month column Q

Thanks.

 

[mart37]

In your loop:
Debug.Print ws.name

In the view Window Direct you can find the names of the sheets. The last one is the sheet with the error.

 

[aaronprudence]

Thank you.

okay, so I stupidly found the issue is with sheets 2 and 12 due to the data in those sheets isn't a date, so obvious now I'm looking at it...

How to I apply the filter to only certain sheets, in this case 3 to 10

 

[mart37]

Something likes this:

  Dim WshtNames As Variant
  Dim WshtNameCrnt As Variant
  WshtNames = Array("Sheet3", "Sheet4", "Sheet5" ........)
  For Each WshtNameCrnt In WshtNames
    With Worksheets(WshtNameCrnt)
    [your code]
    End With
  Next WshtNameCrnt

 

[aaronprudence]

Legend, than you sir.
Very much appreciated!