OK..here we go!!
I've created a marco for you.
Sub datagrab()
Sheets("Store1").Select
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=13, Criteria1:="=Completed", Operator:=xlOr, _
Criteria2:="=Fabricating"
Range("C3:C10000").Select
Selection.Copy
Sheets("Summary").Select
Range("B5").Select
ActiveSheet.Paste
Selection.SpecialCells(xlCellTypeBlanks).Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
End Sub
Add it to a module and attach it to a button and run it.
To explain:
- It goes into your store 1 sheet
- Applies an auto filter
- Filters the status column to only show completed or fabricating
- Copies from the PO column ( C3:C10000)
- Returns to your summary sheet and pastes them into B5 downwards
- Highlights the B column and removes any empty cells so there are no gaps.
I added that last point in as you said you had 8 sheets, so there would be gaps using my above method.
But the last point removes this problem.
Just make sure you have
Selection.SpecialCells(xlCellTypeBlanks).Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
At the end of all 8.
Addtionally you may want to increase/decrease the capture range
Bookmarks