Create Sheets based on values in range

Hello everyone
I have a sheet with data
In range("M13:M " & LastRow) there are values ..
I need to create a sheet for each value and copy the data related to that value to its related sheet
Here's an attachment
The expected results :
Sheets to be created : JD - MD1 - MD2 - MK
and in every sheet the data related to it according to the value in column M

Thanks advanced

Hello YasserKhalil,

Do rows 1 through 12 need to be copied to the new worksheets along with the data?

Thanks Mr. Leith Ross
Just rows (10:12) to be copied in the new sheets
Thanks advanced

Hello YasserKhalil,

Okay, I got it working. The attached workbook has the macro below added to it. Try this out and let me know if it works for you.

Please Login or Register  to view this content.

Hello Mr Leith Ross
Thank you very much for that masterpiece. It's really wonderful ..
and very speedy at running .. thanks a lot for this gift

. Hi YasserKhalil (and Leith Ross)..
. I am still learning and am amazed time and time again how quick the Profi’s work… I had finished one code for you using the Auto Filter method, which was somewhat long. So I began a better one using a method I recently learned from this Thread..
http://www.excelforum.com/showthread...t=#post4082647
. In the meantime Leith has a great code which I have checked and find works perfectly….
.
. For completeness I enclose the first working code, but clearly Leith’s is the best solution.
.
. Mine appears to give exactly the same results as Leith’s.
. For example Here is a small part of your Sheet 1, (As best the screenshot can reproduce it!!)

Using Excel 2007

-	A	B	C	D	E	F	G	H	I	J
4										Customer Code:
5		20GP	???/???????			4325	INVOICE			Pil of loading No:
6		$1,550	?????							Date of shipment:
7						##########	date			Carrier:
8										Container No.:
9										Arrival date:
10	##								5,585
11	#	STYLE No:	CTN NO.	DESCRIPTION	sort_a	sort_b	PCS/CTN	CTNS	QTY(PCS)	UNIT PRICE
12
13	1	MO-1		game	400		16	2	80	72.00
14	2	MO-2		game	400		8	2	40	84.00
15	3	MO-3		game	400		8	2	40	90.00
16	4	MO-4		game	400		8	2	40	96.00
17	5	MO-5		game	400		10	2	50	90.00

Sheet1

…

And after running my code ( or Leith’s ) you get for example this as part of the second newly made sheet:

Using Excel 2007

-	A	B	C	D	E	F	G	H	I	J	K	L	M
1	25								##		0
2	#	STYLE No:	CTN NO.	DESCRIPTION	sort_a	sort_b	PCS/CTN	CTNS	QTY(PCS)	UNIT PRICE	AMOUNT (RMB)		Name
3													??? ??????
4	10	MO-2		game	400		8	1		84.00	0.00		JD
5	9	MO-3		game	400		8	1		90.00	0.00		JD
6	8	MO-4		game	400		8	1		96.00	0.00		JD
7	7	MO-5		game	400		10	1		90.00	0.00		JD
8	6	MO-6		game	400		8	1		102.00	0.00		JD
9	5	14600#		game	400		4	1		105.00	0.00		JD

JD

.. Maybe it could be of some use..
. I was hoping to practice my second solution with this Thread, if you would like a third variation, let me know, or otherwise I will wait and hope I improve my speed for the next Thread of this type!!

Alan

P.s. Code in next Posts… because it is so big!!)

Code corresponding to Post #6

Please Login or Register  to view this content.

Mr. Doc.AElstein
Thank you very very much for this great and wonderful solution you provided
Thanks a lot for sharing it
Regards

Originally Posted by YasserKhalil

Mr. Doc.AElstein
Thank you very very much for this great and wonderful solution you provided
Thanks a lot for sharing it
Regards

. You are very welcome. Appreciate your response

Alan

Hi viewers…
. I appreciate this Thread is solved. But there are two distinct solutions here from Leith and myself. I had been consolidating my knowledge from a third method I learned from here:
http://www.excelforum.com/excel-prog...ell-value.html
.. and I practiced by answering again this particular requirement.
. I thought It would be a reasonable enrichment, without “hijacking” this thread to give then a Third solution for anyone else looking in the future. – ( and if not, then it is a way for me to back up, store and retrieve my stuff as an alternative to “clouding it” or whatever the term is !! )
….

. If I may indulge in explaining the most difficult bit, for my future reference, if no one else’s!!. The code then I give in the next Post #11 and #12. This is because of size limitations in the Thread. The extrme length of the code in this case is caused mainly by my explaining ‘comments. The code is actually short and very efficient in running speed.. ( It uses extensively Arrays, so note format of data will not be included as in the output, as with the other codes )
……

… 1)
. At some point in the program we have obtained an Array containing The entire data field, excluding any headers. ( This is the data in the main sheet, the one that initially is there. ) It is placed in an Array
. - xx()
.
. I obtain a 1 - dimensional “quasi horizontal” Array for unique Look Up Values ( Unique values for the data in column M in this particular example).
. – zz()

. 2) For each of the unique Look Up Values ( zz() ) I …….
. .. obtained a 1 - dimensional “quasi horizontal” Array in which sequentially are listed the “row” indicia ( as strings – but that don’t matter.. ) in which this Look Up Value is found in the Main Data Array.
. - rws()
.
. .. I make a 2 - dimensional, 1 column “vertical” array in which the sequential column indicia for main data is given. In this case 1 ; 4 ; 5 ; 6 ; 7 ; 8 ; 9 ; 10 ; 11 ; 12 ; 13 ( columns A to M ) .
. - clms()

. Now the clever 1 liner that gets output Array for a new sheet in one go..

y() = Application.Index(xx(), rws(), clms())

. I believe the Index is working in some “vector” type form here. VBA “works” as follows here:
. It takes in turn each of the indicies in rws() and for each of these it steps through the indicies in clms(). It returns then effectively a “column” of values.
(This is just the “way” VBA was once programmed to work…but nobody living can actually remember doing it or wrote down anywhere what they did, I think!?!? )
. These values are then the entities in the main Array xx() given by those co-ordinates. In our case then, we initially put into the new Array y(), a column which contains the first data row.
. As this process is then repeated for all the indicies given in rws() we effectively have an Array y() of our required output rows , but transposed. ( so we transpose it back to the correct orientation! )

…………………….
Thanks for watching..
Alan

First part of Code for Post #10

Please Login or Register  to view this content.

Second part of Code for Post #10

Please Login or Register  to view this content.

Mr. Alan
Perfect work.. Excellent at every line as you support explaining for each line in an easy way
Thanks a lot for this great and magic solution

Originally Posted by YasserKhalil

Mr. Alan
Perfect work.. Excellent at every line as you support explaining for each line in an easy way
.....

Glad it could be of use to you,
and thanks again for the feedback