Copy/paste filtered cells to another sheet

[ANE0709]

I have headers in row 1. I am trying to use VBA to filter sheet 1, copy visible cells from A-D not including the headers, and paste in next available row of sheet 3. The code ive used before isnt working this time and im not sure why. Best guess is this time B-C have drop down lists, not sure if that matters.

Sheets("Sheet 1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$FX$100").AutoFilter Field:=3, Criteria1:= _
"=Inactive", Operator:=xlOr, Criteria2:="=Transferred"
Range("A1:D" & lastRow).Offset(1).SpecialCells(xlCellTypeVisible).Select
Selection.copy
Sheets("Sheet 3").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste

 

[mumps]

Try:

Sub test()
    Dim LastRow As Long
    With Sheets("Sheet 1")
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("A1").CurrentRegion.AutoFilter Field:=3, Criteria1:="=Inactive", Operator:=xlOr, Criteria2:="=Transferred"
        .Range("A2:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet 3").Cells(Sheets("Sheet 3").Rows.Count, "A").End(xlUp).Offset(1)
    End With
End Sub

Double check if it should be "Sheet 3" or "Sheet3" (without the space).

 

[ANE0709]

Try:

Sub test()
    Dim LastRow As Long
    With Sheets("Sheet 1")
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("A1").CurrentRegion.AutoFilter Field:=3, Criteria1:="=Inactive", Operator:=xlOr, Criteria2:="=Transferred"
        .Range("A2:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet 3").Cells(Sheets("Sheet 3").Rows.Count, "A").End(xlUp).Offset(1)
    End With
End Sub

Double check if it should be "Sheet 3" or "Sheet3" (without the space).

the debug says this line is an error, just not sure why

Range("A1:D" & lastRow).Offset(1).SpecialCells(xlCellTypeVisible).Select

 

[mumps]

That line of code doesn't exist in the macro I suggested.

 

[ANE0709]

ah, ok. i misunderstood what you wanted me to do. yours worked! thanks!

 

[mumps]

You are very welcome.