Macro to copy and paste value to another worksheet

[Patchbear]

Good day all

Hope someone can help please. I have the following worksheets:

Admin

S/No	ID	Name	Last Taken	Expiry	Remarks
1	12345	ABC	14/12/15	14/12/16	Expired
2	12346	BCA	03/02/16	03/02/17
3	12347	CAB	02/01/16	02/01/17

<tbody>
</tbody>

Ops

S/No	ID	Name	Last Taken	Expiry	Remarks
1	9876	POL	06/04/15	06/04/16	Expired
2	8765	LOP	03/08/16	03/08/17
3	7654	OPL	04/12/15	04/12/16	Expired

<tbody>
</tbody>

Log

S/No	ID	Name	Last Taken	Expiry	Remarks
1	4567	ASW	02/03/16	02/03/17
2	5678	WAS	07/12/15	07/12/16	Expired
3	6789	SAW	04/12/15	04/12/16	Expired

<tbody>
</tbody>

This is my desired outcome on another worksheet. Basically is to copy certain details of employees whose remarks are "expired" in "Admin" worksheet followed by those expired in "Ops" worksheet and so on, with a running serial number.
However, the "Dept" column will be the name of the worksheet that the employee/s belong/s to.

Expired

S/No	ID	Name	Expiry	Dept
1	12345	ABC	14/12/16	Admin
2	9876	POL	06/04/16	Ops
3	7654	OPL	04/12/16	Ops
4	5678	WAS	07/12/16	Log
5	6789	SAW	04/12/16	Log

<tbody>
</tbody>

Thanks in advance

 

[Krayons]

I answered it here: http://www.mrexcel.com/forum/excel-...-copy-paste-certain-criteria.html#post4704059

Others have answered it:

Here: http://www.mrexcel.com/forum/excel-questions/980541-copy-paste-specific-worksheet.html

And here: http://www.mrexcel.com/forum/excel-questions/980097-copy-row-condition.html

And here: http://www.mrexcel.com/forum/excel-questions/551667-macro-move-cells-archive-sheet.html

Use the search function my good man.