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