Collecting data from multiple sheets in wbk
 

Here is the scenario I am trying to solve:
I'm trying to collect onto one sheet the information from the other sheets
in a workbook. Only rows containing certain information from each sheet
needs to be collected. Is there a way to do this simply?

Example of problem:

Data:
Sheet 1 Sheet 2 Sheet 3
col 1 col 2 col 1 col 2 col 1
col 2
row 1 x x
row 2 x x
x
row 3 x
x
row 4 x x
row 5 x x x x

Desired result: (sorting for column 2 in each sheet)
Sheet 4
col 1 col 2
row 1 Sheet 1 row 3
row 2 Sheet 1 row 5
row 3 Sheet 2 row 2
row 4 Sheet 2 row 4
row 5 Sheet 3 row 2
row 6 Sheet 3 row 3

The solution I have right now, which is neither user-friendly nor elegant,
is to copy and "Paste Link" onto a blank sheet all the fields from the
previous sheets. I then use AutoFilter to perform the sorting.