Conditional reference of an enitre row from other worksheets

[basavel]

Hello Excel geniuses

I use excel to track the task delegated to my subordinates. I have a separate tab(worksheet) for each employee. The tabs have common format -one of the columns beings status (open, in progress, finished).


What I'd like to do is to be able to track on a separate master sheet all tasks (and responsible people) that have their status as open or in progress (or not equal to finished). So what I need is something that would scan all the worksheets and if a row has value in "status" column not equal to "finished" display the entire row in a separate worksheet ("unfinished tasks") so that I could easily see who should I chase. I know it might be simpler to put everything into one worksheet and use filters but I'd like to have it in this format. Also there were some instances when I needed that kind of approach in the past and I did not know how to do it so I'd like to learn.

I hope you can help me

Thanks in advance
Bas

 

[ravishankar]

Hi
Assuming status is in col F , paste the following codes in the macro window ( Alt F11)

Sub unfinished()
Sheets.Add.Name = "unfinished"
c = 1
For a = 2 To Sheets.Count
x = Worksheets(a).Cells(Rows.Count, 1).End(xlUp).Row
For b = 1 To x
 If Worksheets(a).Cells(b, 6) <> "finished" Then
 worskheets(a).Rows(b).Copy
 Worksheets("unfinished").Rows(c).PasteSpecial
 c = c + 1
 End If
 Next b
 Next a
End Sub

change cells(b,6) to cells(b,col no of status) if it is not F.
Run the macro. It will insert a sheet called unfinished and lists unfinished jobs from all sheets
Ravi