VBA to Copy Row with Criteria to Another Sheet

[dmlin335]

I am a new VBA coder and am having difficulty executing what is probably a very simple task. Any help would be much appreciated!

In column D of an excel sheet, there are a number of drop-down choices from a list. I am looking to create a button macro that will filter out all cells in Column D that = "Follow-Up". I then want it to copy that entire row to another sheet in the same workbook and add it at the first available blank row. Ideally, each time I hit the button, my list of "Follow-Up" rows gets longer with more data. Below is the code I am using, but nothing is populating in Sheet2. Help please!

Sub NewSheetData()


With Application
.ScreenUpdating = False
.EnableEvents = False
End With


Dim Rng As Range, arCrits(), l As Long


Set Rng = Range([D1], Range("d" & Rows.Count).End(xlUp))
arCrits = Array("Process") 'add more criteria to the array as required


For l = 0 To UBound(arCrits)
On Error Resume Next
With Rng
.AutoFilter , field:=1, Criteria1:=arCrits(l)
.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Copy ["Sheet2"].Range("D" & Rows.Count).End(xlUp).Offset(1)
.AutoFilter
End With
On Error GoTo 0
Next l


Application.EnableEvents = True


End Sub

 

[MarkCBB]

Hi, try recording that process with the auto filter function. I have a similar project I did last year. you should be able to edit that code to work for you.

 

[dmlin335]

Hi, try recording that process with the auto filter function. I have a similar project I did last year. you should be able to edit that code to work for you.

Thanks Mark. Can you clarify what you mean? I have autofilter nested in my loop.