do you know how to connect to an excel workbook as a database?
if so, your problem is very simple
set populate a records set with "SELECT DISTINCT %column% FROM %table%"
where you replace the two tokens by appropraie values
then for each item in the recordset
you run a "SELECT * FROM %table% WHERE %column%='" & item & "'"

are you ok with this ?


"David Macdonald" wrote:

I have a database in one WorkBook and want to transfer filtered data on
setarate sheets in another WorkBook.
One of the columns in the db is a 5 digit number and the sheets in the
destination Workbook are named with the same numbers.
I want to get all the rows containing the sheet name onto the correct sheets.
i.e. All the rows in WB1 referencing 12345 should be transfered to WB2 sheet
12345, all the rows in WB1 referncing 12346 should be transfered to WB2 sheet
12346, you get the idea...
I could just have Excel switch back and forth between the 2 workbooks,
filter, copy and paste OR (I thought) I could place the whole database in an
array, then filter for the results I need as I step through the different
sheets. Now I have my 3000 row x 16 column array BUT I can't figure how to
filter an array...
Should I just give up and let my users get dizzy watching Excel flick
between 2 workbooks for a couple of minutes ?

--
WinXP - Office2003 (Italian)