Thank you guys for the earlier help. I just want to say that my first macro is working; anyway, it is getting the result that I wanted. It's a bit slow, and I have it repeating a bunch of commands that could probably be consolidated. Happy though.

The macro runs automatically when the document is opened. It searches relevant worksheets, and copies them to a data collection worksheet. Data is filtered for repeats and alphabetized. The resulting three columns of data are then named as ranges (not by the macro), then used as drop-down lists.

The second and final macro will take the value selected in one of the drop down boxes, then use it to search the workbook for matching occurrences, then copy target cells from the corresponding row to a data output worksheet. Lastly, I will try to have dates for each search target evaluated (these are certifications), for an output of 'current', 'applied', 'expired'. Having problems, of course, but hope to finish soon.

Anyway, thanks for all the help so far, and for letting me copy your code.

----------------------

Sub Auto_Open()

Dim rCopy As Range


'Search Series Data "IEC wuxi"

Sheets("IEC wuxi").Select

Do

With Worksheets("IEC wuxi")

'copy cells with values in Column B
Set rCopy = .Columns(2).SpecialCells(xlCellTypeConstants)
rCopy.Copy Worksheets("search data").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

'Check row 1000, column 2 for a value; if empty, then proceed to next worksheet.
'This means that the search is only good for 999 items, and this cell should be left blank.

Cells(1000, 2).Select

If ActiveCell = ("") Then Exit Do

End With

Loop

'Search Series Data "TUV wuxi"

Sheets("TUV wuxi").Select

Do

With Worksheets("TUV wuxi")

'copy cells with values in Column A
Set rCopy = .Columns(1).SpecialCells(xlCellTypeConstants)
rCopy.Copy Worksheets("search data").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

Cells(1000, 2).Select

If ActiveCell = ("") Then Exit Do

End With

Loop


'Search Series Data "TUV qinghai"

Sheets("TUV qinghai").Select

Do

With Worksheets("TUV qinghai")

'copy cells with values in Column B
Set rCopy = .Columns(2).SpecialCells(xlCellTypeConstants)
rCopy.Copy Worksheets("search data").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

Cells(1000, 2).Select

If ActiveCell = ("") Then Exit Do

End With

Loop

'search series data "UL wuxi"

Sheets("UL wuxi").Select

Do

With Worksheets("UL wuxi")

'copy cells with values in Column B
Set rCopy = .Columns(2).SpecialCells(xlCellTypeConstants)
rCopy.Copy Worksheets("search data").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

Cells(1000, 2).Select

If ActiveCell = ("") Then Exit Do

End With

Loop


'Search Products Data "IEC wuxi" Column "C"

Do

With Worksheets("IEC wuxi")
'copy cells with values in Column C
Set rCopy = .Columns(3).SpecialCells(xlCellTypeConstants)
rCopy.Copy Worksheets("search data").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)

Cells(1000, 3).Select

If ActiveCell = ("") Then Exit Do

End With

Loop


'Search Products Data "TUV wuxi" Column "B"

Do

With Worksheets("TUV wuxi")
'copy cells with values in Column B
Set rCopy = .Columns(2).SpecialCells(xlCellTypeConstants)
rCopy.Copy Worksheets("search data").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)

Cells(1000, 3).Select

If ActiveCell = ("") Then Exit Do

End With

Loop

'Search Products Data "TUV wuxi" Column "C"

Do

With Worksheets("TUV wuxi")
'copy cells with values in Column C
Set rCopy = .Columns(3).SpecialCells(xlCellTypeConstants)
rCopy.Copy Worksheets("search data").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)

Cells(1000, 3).Select

If ActiveCell = ("") Then Exit Do

End With

Loop