Extracting rows from a table.

[dcgrove]

Hello, the data set in the linked file contains audits performed. My end goal is to have a separate sheet that contains the audits that score less than 66 and the reaudits that occur after the initial <66 score. If a unit scores less than 66, they will be reaudited any number of times until they score greater than 66. The "Vst" column indicates the type of audit each row is. "1" signifies an initial audit, with anything greater than 1 a reaudit. I need the entire row on the new sheet. What's the best way to accomplish this?

https://dl.dropboxusercontent.com/u/...xtraction.xlsx

Thanks for any help you can provide!

Clayton

[Vidya Palani]

Let me know if this helps.

Sub test()
ActiveSheet.AutoFilterMode = False
Range("A1:D10000").AutoFilter
Range("A1:D10000").AutoFilter Field:=4, Criteria1:="<66"
Range(Range("a1"), Range("A1").End(xlToRight).End(xlDown)).Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
End Sub

[dcgrove]

That pulls the the audits that score less than 66, but does not pull the reaudits that occur afterwards. Thanks for giving it a shot though!

[Vidya Palani]

This extracts the rows with audit scores less than 66 and all the rows for which reaudits has happened. Is this what you need?

Sub test()
ActiveSheet.AutoFilterMode = False
Range("A1:D10000").AutoFilter
Range("A1:D10000").AutoFilter Field:=4, Criteria1:="<66"
Range(Range("A1"), Range("A1").End(xlToRight).End(xlDown)).Select
Selection.Copy
Sheets.Add
ActiveSheet.Name = "Data Extract"
ActiveSheet.Paste
Sheets("Data").Activate
Range("A1:D10000").AutoFilter Field:=3, Criteria1:=">1"
Range(Range("A2"), Range("A2").End(xlToRight).End(xlDown)).Select
Selection.Copy
Sheets("Data Extract").Select
Range("a1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Data").AutoFilterMode = False
Sheets("Data").Range("A1").Select
Sheets("Data Extract").Range("A1").Select
End Sub