filter by words and copy to new place

Khoa Tran 0
Viewed 28

I'm doing a filter by words on "DATA" sheet from the word on "Check sector" sheet, then I copy the result to "Check sector" A5 cell. I did the macro, but I didn't work well, can someone help me fix it on the ActiveSheet.Range("$A$1:$D$1000").AutoFilter Field := 4, Criteria1 := Array(,,) _ , Operator := xlFilterValues.

Here is the full code:

Sub Macro12()
'
' Macro12 Macro
'

'
    Range("B3").Select
    Selection.Copy
    Sheets("DATA").Select
    *ActiveSheet.Range("$A$1:$D$1000").AutoFilter Field := 4, Criteria1 := Array(,,) _
        , Operator := xlFilterValues*
    Columns("A:D").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Check SECTOR").Select
    Range("A5").Select
    ActiveSheet.Paste
End Sub
vba excel
1 Answers

Instead of copying the word from "Check SECTOR", you can store the value in a variable & call it while filtering for parameter Criteria1... Below code for reference;

Sub Macro12()

Dim val As String
val = Sheets("Check SECTOR").Range("B3").Value
Sheets("DATA").Select
ActiveSheet.Range("$A$1:$D$1000").AutoFilter Field:=4, Criteria1:=val _
    , Operator:=xlFilterValues
Columns("A:D").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Check SECTOR").Select
Range("A5").Select
ActiveSheet.Paste

End Sub

Hope it helps.

Sachin Kohli0
Related