filter data in the same column by array

[abdelfattah]

hello
i need fixing my code to make filter data based on the same column it supposes bring data after write in four cells e3,f3,g3,h3 i try do that by array but not work

Sub bring_customers()

       Dim CustID As String: CustID = sheet2.[e3].Value
       Dim CustID1 As String: CustID1 = sheet2.[f3].Value
       Dim CustID2 As String: CustID2 = sheet2.[g3].Value
       Dim CustID3 As String: CustID3 = sheet2.[h3].Value
        Dim FromDt As Long: FromDt = sheet2.[d3].Value
        Dim ToDt As Long: ToDt = sheet2.[c3].Value
     Set ar = Array(CustID, CustID1, CustID2, CustID3)
Application.ScreenUpdating = False
      
        sheet2.[A5].CurrentRegion.Offset(1).Clear
      
        With sheet1.[A2].CurrentRegion
        
                .AutoFilter 3, ar
                .AutoFilter 2, ">=" & FromDt, xlAnd, "<=" & ToDt
                .Offset(1).EntireRow.Copy sheet2.Range("A" & Rows.Count).End(3)(2)
                .AutoFilter
        End With
      
Application.ScreenUpdating = True

End Sub

thanks

 

[DanteAmor]

Try this:

Sub bring_customers()
  Dim ar As Range
  Dim FromDt As Long, ToDt As Long
  
  Application.ScreenUpdating = False
  FromDt = Sheet2.[D3].Value
  ToDt = Sheet2.[C3].Value
  Set ar = Sheet2.Range("E3:H3")
  Sheet2.Rows("6:" & Rows.Count).ClearContents
  
  With Sheet1.[A2].CurrentRegion
    .AutoFilter 3, ar.Value, xlFilterValues
    .AutoFilter 2, ">=" & FromDt, xlAnd, "<=" & ToDt
    .Offset(1).EntireRow.Copy Sheet2.Range("A6")
    .AutoFilter
  End With
  
  Application.ScreenUpdating = True
End Sub

 

[abdelfattah]

hi, dante it works but i try to make code copy with headers from sheet1 begins row1 i amended this but the problem repeat the row of header twice from this

    .Offset(1).EntireRow.Copy Sheet2.Range("A6")

to this

    .Offset(0).EntireRow.Copy Sheet2.Range("A6")

 

[DanteAmor]

In wich row do you have the headers?

Try this:

.EntireRow.Copy Sheet2.Range("A6")

 

[abdelfattah]

your legend Dante the code have became more efficient thanks so much

 

[DanteAmor]

Im glad to help you, thanks for the feedback.