VBA TO EXCLUDE CURRENT MONTH

aaronprudence

New Member
Joined
Apr 9, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
The VBA looks fine. Can you check on wich sheet the error occurs? Have that sheet a different table?
 

aaronprudence

New Member
Joined
Apr 9, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.

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

Thanks.
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Apr 9, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Something likes this:

VBA Code:
  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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,984
Messages
5,639,391
Members
417,085
Latest member
Aman2626

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top