code filter data based on month & name doesn't work

[MKLAQ]

hi
I have this data in sheet source it should copy filter data based on two condition
if h1= name then filtering it's ok but if I select only i1 = month and if I select h1,i1 together the code doesn't work

date	name	inv no	case	paid	remained		alan	october
10/01/2021	alan	as123	paid	1000	2200
10/02/2021	john	as124	unpaid	-	1232
10/03/2021	alan	asd123	paid	1000	264
10/04/2021	john	asd124	unpaid	-	264
10/05/2021	roeberto	asd125	paid	1000	264
10/06/2021	sara	asd126	unpaid	-	264
10/07/2021	cali	asd127	paid	1000	264
10/08/2021	caroon	asd128	unpaid	-	264
10/09/2021	canin	asd129	paid	1000	264

'maniacb
Private Sub Worksheet_Change(ByVal Target As Range)
Dim month As String
Dim name As String
Dim lr, i As Long
Dim Rng As Range
Dim k As Integer
Dim source As Worksheet
Dim targetforecastmonth As Worksheet
Application.ScreenUpdating = False
Application.EnableEvents = False
If Not Intersect(Range("H1:I1"), Target) Is Nothing Then

    lr = Cells(Rows.Count, 1).End(xlUp).Row
    Set source = Worksheets("source")
    Set targetforecastmonth = Worksheets("result")
    month = source.Range("I1")
    name = source.Range("H1")
    For i = 1 To lr
        source.Range("G" & i) = source.Range("A" & i)
        source.Range("G" & i).NumberFormat = "mmm"
        source.Range("G" & i) = StrConv(source.Range("G" & i).Text, vbLowerCase)
    Next i

    targetforecastmonth.Cells(2, 1).CurrentRegion.Clear
    Set Rng = source.Range(Cells(1, 1), Cells(lr, 7))
    With Rng
        .AutoFilter
        If name <> "" Then
            .AutoFilter 2, Criteria1:=name
        End If
        If month <> "" Then
            .AutoFilter 7, Criteria1:=month
        End If
        .SpecialCells(xlCellTypeVisible).Copy
        targetforecastmonth.[A1].PasteSpecial xlPasteAll
        .AutoFilter
    End With

    source.Range("G1:G" & lr).Value = ""
    targetforecastmonth.Range("G1:G" & lr).Value = ""
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[MKLAQ]

but it is still the error in word "month"

 

[mart37]

Dim Month as String

 

[MKLAQ]

nothing shows I choose more than month and I'm sure about my format date mm/dd/yyyy

 

[MKLAQ]

I fixed , you indicate to year 2021 but my year 2020 that's why nothing shows
thanks buddy for your assistance

 

[MKLAQ]

I have another problem what I say is mystery the month of December doesn't show
do you have any idea?

 

[mart37]

A problem with the dateformat: 12-1-2021 ==> 1-12-2021 (12 january 2021)

A solution without day and year:

Sub test()
    Dim month As String
    Dim monthnumber As Integer ' instead of string
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    Set Source = Worksheets("source")
    month = Source.Range("I1")
    arr = Array("january", "february", "march", "april", "may", "june", "july", "august", "september", "october", "november", "december")
    monthnumber = Application.Match(month, arr, False)
    Set Rng = Source.Range(Cells(1, 1), Cells(lr, 7))
    With Rng
         .AutoFilter Field:=1, Operator:=xlFilterDynamic, Criteria1:=20 + monthnumber
     End With
End Sub

 

[MKLAQ]

well done ! thanks so much