Here's one play to automate it using non-array formulas ..

A sample construct is available at:
http://www.savefile.com/files/5143636
AutoFiltering_Data_To_Resp_Sheet_NonArrayFormulas.xls

In sheet: WS1 (the "master sheet")
------------------------------------------
Assume data in cols A to B, data in row2 down,
with the key col = col A (Product #)

Put in I2, copy down to say I20,
to cover the max expected extent of data in col A:
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"",ROW()))
(Leave I1 empty)

Put in J1, copy across to say Q1,
to cover the max expected no. of unique products:
=IF(COLUMN(A1)>COUNT($I:$I),"",TEXT(INDEX($A:$A,MATCH(SMALL($I:$I,COLUMN(A1)),$I:$I,0)),"000"))

Put in K2: =IF(J$1="","",IF(TEXT($A2,"000")=J$1,ROW(),""))
Copy K2 across to Q2, fill down to Q20
to cover the max expected extent of data in col A (as per col I)

Click Insert > Name > Define
Put under "Names in workbook:":   WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above will define WSN as a name we can use refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan.

In a new sheet named as: 123
With the same col headers pasted into A1:B1

Put in A2:
=IF(ROW(A1)>COUNTIF(WS1!$A:$A,WSN),"",
INDEX(WS1!A:A,MATCH(SMALL(OFFSET(WS1!$I:$I,,MATCH(WSN,WS1!$J$1:$IV$1,0)),ROW(A1)),
OFFSET(WS1!$I:$I,,MATCH(WSN,WS1!$J$1:$IV$1,0)),0)))

Copy A2 across to B2, fill down to say B10,
to cover the max expected # of lines for any one product
(Fill down by the smallest extent sufficient to cover the max expected # of
lines for any one product. Here, I've assumed that 9 rows (rows 2 to 10) is
sufficient.)

Cols A to B will return only the lines for product: 123 from "WS1",
with all lines neatly bunched at the top.

Now, just make a copy of the sheet: 123, rename it as the next product: 156,
and we'd get the results for that product. Do note that if the product# is
less than 3 digits, ie < 100, we need to name it (the sheet tab) with leading
zeros. Eg: 099 for product 99, 008 for product 8, etc.  

Repeat the copy > rename sheet process to get the rest of the product sheets
(a one-time job).

Adapt to suit ..