Perhaps you'd like to try this formulas approach ..
In Sheet1
-------------
Assume the sample source table below is in cols A to D
data from row2 down, with the key column "Status" in col D, viz:
SN Name Desc Status
01 Name1 Data1 Incomplete
02 Name2 Data2 Complete
03 Name3 Data3 Incomplete
04 Name4 Data4 Complete
etc
Using 2 empty cols to the right, say cols F and G,
list in F1:G1, the 2 statuses: Complete, Incomplete
Put in F2: =IF($D2="","",IF($D2=F$1,ROW(),""))
Copy F2 across to G2, then fill down by the max expected number of rows of
data in cols A to D, say down to G200?
In sheet named: Complete
-------------------------------------
Let's reserve cell A1 to pull in the sheetname
Put in A1: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)
(This'll extract the sheetname into A1.
But you need to save the file first.)
Paste the same col headers from Sheet1 into A2:C2, viz.:
SN, Name, Desc
Put in A3:
=IF(ISERROR(SMALL(OFFSET(Sheet1!$E:$E,,MATCH($A$1,Sheet1!$F$1:$G$1,0)),ROWS(
$A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMALL(OFFSET(Sheet1!$E:$E,,MATCH($A$1,S
heet1!$F$1:$G$1,0)),ROWS($A$1:A1)),OFFSET(Sheet1!$E:$E,,MATCH($A$1,Sheet1!$F
$1:$G$1,0)),0)))
(normal ENTER will do)
Note: You'd need to correct/restore the inadvertent line wraps
/ line breaks when you copy > paste the above formula into A3
Copy A3 across to C3, then fill down by as many rows
as was done in Sheet1, i.e. down to C201
You'll see that cols A to C (in row3 down)
will auto-return the "filtered" rows from Sheet1
for the status: Complete, i.e. for the sample data-set above,
it'll appear as:
SN Name Desc
02 Name2 Data2
04 Name4 Data4
(rest are blank [""] rows)
Now just duplicate/make a copy of the sheet: Complete,
rename it as: Incomplete
and you'll get the "filtered" rows for Incomplete, viz.:
SN Name Desc
01 Name1 Data1
03 Name3 Data3
(rest are blank [""] rows)
Adapt / extend to suit ..
