VBA to find more than 1 value, copy and paste to anther sheet

[amxtomzo]

Hello and thanks for looking
I am trying to loop through a spreadsheet looking in column 3 for DC01, DC02, DC03...ect
and in between the DCXX, in column B, i am also looking for EDITION,
i need to make a list with the DCXX, followed by each instance of EDiTION and the 8 columns of data
listed in between each instance if DCXX

the DCXX goes from DC01 to DC250

i can only find one or the other
i cant seem to start looking for EDITION when i find DCXX,
then stop looking for EDITION when i find the next DCXX
I can only get all of the DCXX listed, Or all of the EDITIONS listed

Thank you very much

it should look like the following when its done, only a much longer list

	A	B	C	D	E	F	G	H
2	DC01
3		EDITION	1	1	1	2	1	1
4		EDITION	3	625	4	640	1266	12504
5	DC02
6		EDITION	5	5	1	2	5	5
7		EDITION	3	10	1	10		10
8		EDITION	4	279	4	285	565	5584
9		EDITION	5	605	1	625	1231	12101
10	DC03
11		EDITION	2	155	19	164	320	3119
12		EDITION	5	437	8	451	889	8748

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 64px"> <col style="WIDTH: 73px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"></colgroup> <tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[JLGWhiz]

Will DC01 or DC02 or any of the DCXX values occur multiple times in the file?

 

[amxtomzo]

No there is only 1 of each
they number from DC01 to DC250

 

[JLGWhiz]

I'm having trouble visualizing what your worksheet would look like. Can you post a screen shot or a link to a share server of post a mock up illustration?

 

[amxtomzo]

Give me a moment and i will throw something to gether
i tried to paste an image i made with excel Jeenie

i can see it, can you?

 

[amxtomzo]

How do you feel about DROPBOX?

this is the public folder with a mock up of what it should look like

https://www.dropbox.com/sh/4xfy48hriyncymi/AAAvUeE9gOiECHquHiptWGs7a?dl=0

 

[amxtomzo]

here the image ....I hope

	A	B	C	D	E	F	G	H
3	DC01
4		EDITION	1	1	1	2	1
5		EDITION	3	625	4	640	1266	12504
6		EDITION	5	5	1	2	5
7	DC02
8		EDITION	3	10	1	10
9		EDITION	4	279	4	285	565	5584
10		EDITION	5	605	1	625	1231	12101
11	DC03
12		EDITION	2	155	19	164	320	3119
13		EDITION	5	437	8	451	889	8748
14	DC04
15		EDITION	1	145	5	141	287	2905
16		EDITION	2	46	17	47	94	937
17		EDITION	4	304	1	325	630	6081
18	DC05
19		EDITION	1	57	16	59	117	1156
20		EDITION	2	47	3	47	95	943
21		EDITION	3	84	2	86	171	1682
22		EDITION	4	88	5	88	177	1765
23		EDITION	5	142	5	146	289	2845
24	DC07
25		EDITION	1	32	12	38	71	652
26		EDITION	1	2	12	3	52
27		EDITION	2	2	12	3	52
28		EDITION	3	3	2	4	62
29		EDITION	4	3	2	4	62
30		EDITION	5	20	7	33	54	407
31		EDITION	2	13	2	3	13
32		EDITION	4	1	1	2	1

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 64px"> <col style="WIDTH: 73px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"></colgroup> <tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[JLGWhiz]

I think we have a miscommunication. I need to know what your starting worksheet looks like. I understand what you want for results, but my problem is in not knowing where data exists on the source document, so I can write the code to accomodate that data layout. I understand that the DC01-DC250 is in column C and the Editions are in column B. What I don't know is what else might be in those columns and if there are blanks between groupings, etc. I need to be able to tell VBA where to look for the data you want, how to identify it and what to do with it. I assume you want output to sheet 2.

 

[amxtomzo]

sorry
got put on another project
This is a piece of the starting spreadsheet...
Yes there are Blank Spaces in Column B and C
Yes Sheet 2 is where it should go,
I put a copy part of the spreadsheet in my public dropbox folder
https://www.dropbox.com/sh/4xfy48hriyncymi/AAAvUeE9gOiECHquHiptWGs7a?dl=0

it goes as high as DC07

	A	B	C	D	E	F	G	H	I	J
16		PARM	RUN	DATE	IS:	THURSDAY	JUNE	18,	2015
17
18		----------------------------------------	-------------BUNDLES------------	#NAME?	INFORMATION--------------
19		#	OF	#	OF	KEY	INST	TOTAL	TOTAL	BUND
20		PUBL	TRUCK	BLNK	KEY	XTRA	XTRA	BUND	DRAW	DESCRIPTION
21
22		650	1211	DIST	661	1071	CARRIER	WEST	RELAY	2
23		660	1212	DIST	661	1071	SC	WEST	RELAY	4
24		670	1221	DIST	661	1077	CARRIER	WEST	RELAY	5
25		680	1231	DIST	661	1085	CARRIER	WEST	RELAY	27
26		690	1233	DIST	661	1085	NIE	WEST	RELAY	15
27		AM	DC01	WEST	DC	623	19	591	12479	CARRIER
28		34	DEALER	20	SEP	PRED	670
29		EDITION	1	1	1	2	1
30		EDITION	3	623	13	638	1262	12473
31		EDITION	5	5	1	2	5
32		10	2001	CENT	DC	1109	CARR	CENT	RELAY	8
33		1400	2532	DIST	5036	1118	RACK	CENT	RELAY	16
34		5	1	DEALER	20	SEP	PRED	15	Circulation	1.8
35	RCIR8205	T	R	U	C	K	M	A	N	I
36	LOCATION	SPGF
37		PARM	RUN	DATE	IS:	THURSDAY	JUNE	18,	2015
38
39		----------------------------------------	-------------BUNDLES------------	#NAME?	INFORMATION--------------

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"></colgroup> <tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[JLGWhiz]

That was what I needed to see. On the first look, it appears to be too randomly organized for me to write a simple macro for. I have some other work to do right now, and will take another look later, but don't get your hopes up on this one.