Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

help solve filter data from sheet to another based on month

0

hello 

i have  this  code   but  i  no  know  what's  the  problem 

if  i  filter  data  besd  on  name  in h1 it's  ok  but  if  i  select  the only month in i1

or select  name and month  h1,i1  it  alway  show  the  message box  

by  the  way  format  date  mm/dd/yyyy

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("data").Cells(1).CurrentRegion
        .Rows(1).Copy Sheets("master").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("master").Cells(1).CurrentRegion
        .Parent.[m2].Clear
        If Sheets("master").Cells(1).CurrentRegion.Rows.Count = 1 Then
            MsgBox "No data found": Exit Sub
        End If
        Sheets("master").Select
    End With
End Sub

please if  anybody  can  help  me 

excel
MS Office Version: 2010
posted by: speed (rep: 40) on Thu Feb 4, 2021 at 9:23 am
Answer
Discuss

Answers

0
Selected Answer

Hi Speed

The problem is the dates in column A and your line:

If IsNumeric(myMonth) Then txt = "year(a2)=year(today()),month(a2)=" & myMonth
You match the month with i1(Data) but eventually AND dates in column A with the year of today (the bit in bold above) i.e 2021 but all dates in A are 2020! Therefore it filters to no data (since one argument of the AND is FALSE from the failed year match).

I changed all the dates in A to Feb 2021 dates (and i1 to "feb") and it filters correctly with a "number client" in h1.

Suggest however that you add another fitler crtieria for the year.

Revision 1: You could do the year filter by putting the year in J1 of data (add some data validation) then use this code (Ket changes in bold) to do the filter (if both years and month are set) and get more info once that's done:

Sub test()
    Dim myMonth, myMonths, myYear, txt As String
    myMonths = Array("jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec")
    Sheets("master").UsedRange.Clear
    With Sheets("data").Cells(1).CurrentRegion
        .Rows(1).Copy Sheets("master").Cells(1)
        myMonth = Application.Match(.Parent.Range("i1"), myMonths, 0)
        myYear = .Range("j1").Value
        If IsNumeric(myMonth) And IsNumeric(myYear) Then txt = "year(a2)=" & myYear & ", 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("master").Cells(1).CurrentRegion
        .Parent.[m2].Clear
        If Sheets("master").Cells(1).CurrentRegion.Rows.Count = 1 Then
            MsgBox "No data found": Exit Sub
        End If
     End With
        Sheets("master").Select
     With Sheets("data")
        MsgBox "Filtered data for Month=" & .Range("i1") & ", Year=" & myYear & ", " & .Range("B1") & "=" & .Range("H1")
    End With
End Sub

Hope this helps you fix your code.

posted by: John_Ru (rep: 6152) on Thu Feb 4, 2021 at 10:57 am
Discuss

Discussion

awful!! how  i'm  stupid  , it  made  cuased  the  headache and  barely  lost  my  mind   thanks  for  your  an important  notice   ,how  the another  filter crtieria    should  be    and  which   line   indeed   this  code  not  mine  i'v  found   it  in the  internet  to  work  with  what  i  need 
speed (rep: 40) Feb 4, '21 at 11:48 am
Don't feel bad- we all do it.

See Revision 1 but note that I think it's not really our role to fix code you find on the internet.
John_Ru (rep: 6152) Feb 4, '21 at 1:05 pm
thanks for your cooperate but it gives me error in this line 
.Parent.[m2].Formula = "=and(" & txt & ")"
application defined or object defined error
speed (rep: 40) Feb 4, '21 at 1:23 pm
Speed- I only get that error with if I put a month in  i1 but NO year in J1 (which was the point of the change- and I asked you to sort out data validation. Enter a year in J1 and it will work.
John_Ru (rep: 6152) Feb 4, '21 at 2:24 pm
yes you're  right   and  sorry  about  it    
many  thanks   for   your help 
speed (rep: 40) Feb 4, '21 at 2:47 pm
No problem, Speed
John_Ru (rep: 6152) Feb 4, '21 at 3:21 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login