fix code search by dropdown and get data from sheet to another

alhagag · Jul 5th 2020

hi

i need solve this code doesn't work no showing any data in sheet(account) from sheet(data) in sheet account i have to choose client in b1 by dropdown and write from date in b2 and b3 to date then run macro show the data in sheet (aacount)

this is my code

Code

Sub sama1()
Dim LastR As Long
Dim SText As String
Dim StDate As Date
Dim EndDate As Date
Dim LastR1 As Long
Application.ScreenUpdating = False
Sheets("account").Range("A5:h10000").ClearContents
SText = Sheets("account").Range("B1")
StDate = Sheets("account").Range("B2")
EndDate = Sheets("account").Range("B3")
LastR1 = Sheets("DATA").Cells(Rows.Count, 2).End(xlUp).Row
Sheets("DATA").Range("A3:g" & LastR1).AutoFilter Field:=7, Criteria1:=SText
    Sheets("DATA").Range("A3:g" & LastR1).AutoFilter Field:=2, Criteria1:=">=" & Format(StDate, "mm/dd/yy"), Operator:=xlAnd, _
    Criteria2:="<=" & Format(EndDate, "mm/dd/yy")
LastR = Sheets("DATA").Cells(Rows.Count, 2).End(xlUp).Row
Sheets("DATA").Range("A3:g" & LastR).SpecialCells(xlCellTypeVisible).Copy
    Sheets("account").Range("A5").PasteSpecial
    Sheets("account").Range("A5").Select
    Sheets("DATA").Range("A3:g3").AutoFilter
Application.ScreenUpdating = True
End Sub

Display More

alhagag · Jul 5th 2020

this is my file

**royUK** · Jul 6th 2020

Use AdvancedFilter with VBA

alhagag · Jul 6th 2020

thanks Royuk but it shows all data as you see in b1 choose the name and from date to date so it supposses getting a data related name and date not get all of data

dangelor · Jul 7th 2020

Try changing the field in this line

Code

Sheets("DATA").Range("A3:g" & LastR1).AutoFilter Field:=7, Criteria1:=SText

equal to 6.

alhagag · Jul 7th 2020

hi, dangelor i have changed but it gives me error 1004 in the same changed line code

dangelor · Jul 8th 2020

account.xlsm

See attached file

alhagag · Jul 8th 2020

thanks dangelor yes this is what i'm looking for but i have last request i would when dropdown is empty so it shows all of data

dangelor · Jul 8th 2020

Possibly...

Code

Sub sama1()
    Dim LastR As Long
    Dim SText As String
    Dim StDate As Date
    Dim EndDate As Date
    Dim LastR1 As Long
    Application.ScreenUpdating = False
    Sheets("account").Range("A5:h10000").ClearContents
    
    SText = Sheets("account").Range("B1")
    If SText = "" Then SText = "*"
    StDate = Sheets("account").Range("B2")
    If StDate = 0 Then StDate = WorksheetFunction.Min(Sheets("DATA").Columns(2))
    EndDate = Sheets("account").Range("B3")
    If EndDate = 0 Then EndDate = WorksheetFunction.Max(Sheets("DATA").Columns(2))

    LastR1 = Sheets("DATA").Cells(Rows.Count, 2).End(xlUp).Row
    Sheets("DATA").Range("A3:g" & LastR1).AutoFilter Field:=6, Criteria1:=SText
    Sheets("DATA").Range("A3:g" & LastR1).AutoFilter Field:=2, Criteria1:=">=" & Format(StDate, "mm/dd/yy"), Operator:=xlAnd, _
                                Criteria2:="<=" & Format(EndDate, "mm/dd/yy")
    LastR = Sheets("DATA").Cells(Rows.Count, 2).End(xlUp).Row
    Sheets("DATA").Range("A3:g" & LastR).SpecialCells(xlCellTypeVisible).Copy
    Sheets("account").Range("A5").PasteSpecial
    Sheets("account").Range("A5").Select
    Sheets("DATA").Range("A3:g3").AutoFilter
    Application.ScreenUpdating = True
End Sub

Display More

alhagag · Jul 9th 2020

perfect , thanks dangelor for your assistance

dangelor · Jul 9th 2020

You're welcome!

fix code search by dropdown and get data from sheet to another

Participate now!

Share