Filter date range by selecting the month

[rasika_99]

I have an Excel file with a Table. It contains a column that contains the expiry dates of policies. I have a dropdown menu above the table containing the all months (Jan –Dec). When I select a month, I need to filter the records relating to that month and copy to a new sheet.

How can I write a vba code for this?

 

[kevin9999]

You've provided very little information to work with, so the following is provided for demonstration purposes only. It makes lots of assumptions - you have an actual table (not just a range) and the data is set out as shown below. You'll have to change everything to fit your actual data layout.

Running this code:

Option Explicit
Sub FilterTableCopy()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Sheet1")          '<-- *** Change sheet names to suit ***
    Set ws2 = Worksheets("Sheet2")
    Dim Mnth As Long
    Mnth = Application.Evaluate("=MONTH(1&" & Chr(34) & ws1.[B1] & Chr(34) & ")") + 20
    
    With ws1.ListObjects("Table1")
        .Range.AutoFilter 2, Mnth, 11
        If WorksheetFunction.Subtotal(3, ws1.Range("B3", ws1.Cells(Rows.Count, "B").End(xlUp))) > 1 Then
            .DataBodyRange.SpecialCells(xlCellTypeVisible).Copy ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
        Else
            MsgBox "No records found for this month"
            .AutoFilter.ShowAllData
            Exit Sub
        End If
        .AutoFilter.ShowAllData
    End With
End Sub

With this on sheet 1:

Filter Table.xlsm
	A	B
1	Month:	Jul
2
3	Policy #	Expiry date
4	1015	31-Aug-24
5	1092	25-Jul-24
6	1092	03-Jan-24
7	1076	01-Sep-24
8	1010	29-May-24
9	1039	02-Jun-24
10	1086	19-Sep-24
11	1020	24-Apr-24
12	1083	27-Apr-24
13	1082	24-May-24
14	1009	03-Sep-24
15	1081	31-Jul-24
16	1031	26-May-24
17	1078	15-Jul-24
18	1078	20-Nov-24
19	1030	11-Sep-24
20	1049	02-Jun-24
21	1088	30-Oct-24
22	1036	04-Sep-24
23	1069	06-Jun-24
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B1	List	Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec

Will give you this on sheet 2 (headers already in place):

Filter Table.xlsm
	A	B
1	Policy #	Expiry date
2	1092	25-Jul-24
3	1081	31-Jul-24
4	1078	15-Jul-24
5
Sheet2