copy data from sheet to another based on two dropdown selection

hello
i try making code copy data from sheet to another based on two dropdown selection h1=name and i1 = month what i would when i select month and name it brings data relating the name and month and if i select month and no selected name bring data relating month and if i select only name without month then bring data name and if no selected month and name then bring all data

HTML Code: 

thanks nankw83 it works but not what i want still things missed when i select name and empty month it gives me error and the same thing with the month and the name are empty the right when i select only name then ignore the month if the month and name are empty then copy all of data and i note if i select only month for instance dec then copy all of data of months of course is wrong it should copy what i select the only dec month

Try

Sub FilterCopy()
Dim month As String
Dim name As String
Dim lr As Long
Dim Rng As Range

Dim k As Integer
Dim source As Worksheet
Dim targetforecastmonth As Worksheet
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"
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 Sub

hi, nankw it remains one thing when i select dec month it also copy oct, nov this is not what i want i would copy what i choose specific month if i select dec then copy only dec without copy months

yes is very weird i checked your file and i see you result month nov but when i run the problem continues i just last thing if you have time this is my mistake i had making clear from the beginning if is possible do that in worksheet change event thanks for your time

actually still the problem continues , is it version office relating that ?

I get the same thing, try

   If .[i1] = "" Then FromDate = "" Else FromDate = ">=" & CLng(DateValue("1-" & .[i1].Value))

thanks Fluff now it works actually if i have no data in specific month it gives me error i fixed by on error resume next but if is possible instead of brings me message box this month doesn't contain data

Assuming years of dates in col.A are all current year.

Sub test()
    Dim myMonth
    Sheets("result").UsedRange.Clear
    With Sheets("source").Cells(1).CurrentRegion
        .Rows(1).Copy Sheets("result").Cells(1)
        myMonth = Application.Match(.Parent.Range("i1"), [text(row(1:12)*29,"mmm")], 0)
        If IsNumeric(myMonth) Then
            .Parent.[m2].Formula = "=and(year(a2)=year(today()),month(a2)=" & myMonth & ",b2=h$1)"
            .AdvancedFilter 2, .Parent.[m1:m2], Sheets("result").Cells(1).CurrentRegion
            .Parent.[m2].Clear
            Sheets("result").Select
        End If
    End With
End Sub

hi, jindon did you test your code in attached file ? it doesn't work for me it doesn't show anything in sheet (result)

Of course it is working here, but I just realized that the month names in your country is not like jan, feb, mar etc...

Try change to

Sub test()
    Dim myMonth, myMonths
    myMonths = Array("jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec")
    Sheets("result").UsedRange.Clear
    With Sheets("source").Cells(1).CurrentRegion
        .Rows(1).Copy Sheets("result").Cells(1)
        myMonth = Application.Match(.Parent.Range("i1"), myMonths, 0)
        If IsNumeric(myMonth) Then
            .Parent.[m2].Formula = "=and(year(a2)=year(today()),month(a2)=" & myMonth & ",b2=h$1)"
            .AdvancedFilter 2, .Parent.[m1:m2], Sheets("result").Cells(1).CurrentRegion
            .Parent.[m2].Clear
            Sheets("result").Select
        End If
    End With
End Sub

well done , maniacb your code works as what i want sorry i'm too late to answer you

still missed something when month and name are empty it gives me error it supposes copying all of data

Ahhh, missed that.
Add one line in bold.

Sub test()
    Dim myMonth, myMonths, myName, txt As String
    myMonths = Array("jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec")
    Sheets("result").UsedRange.Clear
    With Sheets("source").Cells(1).CurrentRegion
        .Rows(1).Copy Sheets("result").Cells(1)
        myMonth = Application.Match(.Parent.Range("i1"), myMonths, 0)
        If IsNumeric(myMonth) Then txt = "year(a2)=year(today()),month(a2)=" & myMonth
        If .Parent.[h1] <> "" Then txt = txt & IIf(txt <> "", ",", "") & "b2=h$1"
        If Len(txt) Then .Parent.[m2].Formula = "=and(" & txt & ")"
        .AdvancedFilter 2, .Parent.[m1:m2], Sheets("result").Cells(1).CurrentRegion
        .Parent.[m2].Clear
        Sheets("result").Select
    End With
End Sub

thanks jindon every thing is ok i know my demands are so much but i have last thing if i have no data in specific month i would show message inform me " there is no data for this month"
thanks again

Add lines in bold.

Sub test()
    Dim myMonth, myMonths, myName, txt As String
    myMonths = Array("jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec")
    Sheets("result").UsedRange.Clear
    With Sheets("source").Cells(1).CurrentRegion
        .Rows(1).Copy Sheets("result").Cells(1)
        myMonth = Application.Match(.Parent.Range("i1"), myMonths, 0)
        If IsNumeric(myMonth) Then txt = "year(a2)=year(today()),month(a2)=" & myMonth
        If .Parent.[h1] <> "" Then txt = txt & IIf(txt <> "", ",", "") & "b2=h$1"
        If Len(txt) Then .Parent.[m2].Formula = "=and(" & txt & ")"
        .AdvancedFilter 2, .Parent.[m1:m2], Sheets("result").Cells(1).CurrentRegion
        .Parent.[m2].Clear
        If Sheets("result").Cells(1).CurrentRegion.Rows.Count = 1 Then
            MsgBox "No data found": Exit Sub
        End If
        Sheets("result").Select
    End With
End Sub

excellent i just understand this part of code it supposes showing formula in col m but nothing is existed am i right or wrong?

When both J1 & I1 are blank, M2 should have no formula, means no condition, so full extraction.
Check after the data transfer, if any row(s) are added below the header, record(s) are transferred, otherwise No data found.

HTH

hi, jindon actually whether month & name are empty or not it doesn't show the formula it 's not important it's minor and curiosity from me the most important copy data based on two selection and multiple condition
i would thank you and nank maniacb , fluff for your codes and assistance
best regards,
abdelfattah

M2 is covered by the Button, so you can not see.
Move the button somewhere else and do a step debug, so that you can see the formula when one of H1:I1 cell is/are filled.

before i issue post 25 i thought that like you i moved the button but nothing happened

Impossible,
You are not doing a step debug...
Fill H1 or I1 or both and run then code.

Sub test()
    Dim myMonth, myMonths, myName, txt As String
    myMonths = Array("jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec")
    Sheets("result").UsedRange.Clear
    With Sheets("source").Cells(1).CurrentRegion
        .Rows(1).Copy Sheets("result").Cells(1)
        myMonth = Application.Match(.Parent.Range("i1"), myMonths, 0)
        If IsNumeric(myMonth) Then txt = "year(a2)=year(today()),month(a2)=" & myMonth
        If .Parent.[h1] <> "" Then txt = txt & IIf(txt <> "", ",", "") & "b2=h$1"
        If Len(txt) Then .Parent.[m2].Formula = "=and(" & txt & ")"
        Stop
        .AdvancedFilter 2, .Parent.[m1:m2], Sheets("result").Cells(1).CurrentRegion
        .Parent.[m2].Clear
        If Sheets("result").Cells(1).CurrentRegion.Rows.Count = 1 Then
            MsgBox "No data found": Exit Sub
        End If
        Sheets("result").Select
    End With
End Sub

i did and highlight in stop and show true in m2

Then select M2 to see the formula in the formula bar.

Here is my version of the code as a worksheet_change event, as you requested above.

Option Explicit

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
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"
    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
End Sub

thanks maniacb but it doesn't show anything except the headers

Here is the code in the workbook.

yes it works but not all of cases for instance if i select month and name does not work and if i select only month doesn't work so your code works only two cases first when select only name the second when the month and name are no selected
i hope fixing

I capitalized the month values on your sheet to make the code work. Jindon and Nankw83 solutions don't have this limitation.

jindon did it in post 20# and before it i made clear you can check it
thanks again

many thanks but i have note this is not firs problem face about writing month every code depends on month doesn't work if writing month is not match with office language so in the beginning the code doesn't work if the month by English my office is Arabic then works , is it problem from language or other problem ?