Auto-create separate worksheets...

[craig72]

I have a list in a single worksheet that runs as follows:

Product no. Qty.
123 17
123 8
123 12
156 11
184 0
184 13

and so on.

I have used a function previously that allows a new worksheet to be created within the workbook for each product no. Rather than cutting and pasting each block of product numbers into its own worksheet, there is a way to automatically create a new worksheet that, for example, contains all the 123 product data in worksheet 1, all the 156 product data in worksheet 2 etc.

Any ideas how to do it as I can't remember and can't find anything related to it in the Excel help file?

Thanks in advance.

[Ron de Bruin]

See
http://www.rondebruin.nl/copy5.htm

Try
http://www.rondebruin.nl/copy5.htm#all


--
Regards Ron De Bruin
http://www.rondebruin.nl



"craig72" <[email protected]> wrote in message
news:[email protected]...
>
> I have a list in a single worksheet that runs as follows:
>
> Product no. Qty.
> 123 17
> 123 8
> 123 12
> 156 11
> 184 0
> 184 13
>
> and so on.
>
> I have used a function previously that allows a new worksheet to be
> created within the workbook for each product no. Rather than cutting
> and pasting each block of product numbers into its own worksheet, there
> is a way to automatically create a new worksheet that, for example,
> contains all the 123 product data in worksheet 1, all the 156 product
> data in worksheet 2 etc.
>
> Any ideas how to do it as I can't remember and can't find anything
> related to it in the Excel help file?
>
> Thanks in advance.
>
>
> --
> craig72
> ------------------------------------------------------------------------
> craig72's Profile: http://www.excelforum.com/member.php...o&userid=35540
> View this thread: http://www.excelforum.com/showthread...hreadid=553034
>

[craig72]

Thanks for your reply. I remember that there is a menu option somewhere in Excel that performs that function for you instead of having to use VBA or macros etc. Does anyone know if that is the case or not? I'm not too sure how to use macros and VBA.

Thanks.

[Max]

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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"craig72" wrote:
> I have a list in a single worksheet that runs as follows:
>
> Product no. Qty.
> 123 17
> 123 8
> 123 12
> 156 11
> 184 0
> 184 13
>
> and so on.
>
> I have used a function previously that allows a new worksheet to be
> created within the workbook for each product no. Rather than cutting
> and pasting each block of product numbers into its own worksheet, there
> is a way to automatically create a new worksheet that, for example,
> contains all the 123 product data in worksheet 1, all the 156 product
> data in worksheet 2 etc.
>
> Any ideas how to do it as I can't remember and can't find anything
> related to it in the Excel help file?