Another Excel macro question

[steveo275]

Hello,

I've asked about something similar before here:

Excel Macro question.

I have a similar request. The last one worked great. However, I tried to figure out the macro and how to expand it but it's a bit beyond me.

I need my "Header" sheet:

Header

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

H

IN

*

*

Invoice

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

CAP

*

*

*

*

*

Yes

H

IN

*

*

Invoice

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

CAP

*

*

*

*

*

Yes

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Ref Key[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Customer[/TD]
[TD="align: center"]Ref Type[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]PO[/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]Currency[/TD]
[TD="align: center"]Sales Type[/TD]
[TD="align: center"]Ref Date[/TD]
[TD="align: center"]Due Date[/TD]
[TD="align: center"]Terms[/TD]
[TD="align: center"]Ship To Name[/TD]
[TD="align: center"]Add1[/TD]
[TD="align: center"]Add2[/TD]
[TD="align: center"]Add3[/TD]
[TD="align: center"]City[/TD]
[TD="align: center"]State[/TD]
[TD="align: center"]Zip[/TD]
[TD="align: center"]Country[/TD]
[TD="align: center"]Phone[/TD]
[TD="align: center"]Fax[/TD]
[TD="align: center"]Billto[/TD]
[TD="align: center"]BTAdd1[/TD]
[TD="align: center"]BTAdd2[/TD]
[TD="align: center"]BTAdd3[/TD]
[TD="align: center"]BTCity[/TD]
[TD="align: center"]BTState[/TD]
[TD="align: center"]BTZip[/TD]
[TD="align: center"]BTCountry[/TD]
[TD="align: center"]BTPhone[/TD]
[TD="align: center"]BTFax[/TD]
[TD="align: center"]Discount[/TD]
[TD="align: center"]Tax[/TD]
[TD="align: center"]Sales Loc[/TD]
[TD="align: center"]Notes[/TD]
[TD="align: center"]Station[/TD]
[TD="align: center"]Related[/TD]
[TD="align: center"]Rep1[/TD]
[TD="align: center"]Rep2[/TD]
[TD="align: center"]Period[/TD]
[TD="align: center"]Year[/TD]
[TD="align: center"]Exempt[/TD]
[TD="align: center"]Exempt ID[/TD]
[TD="align: center"]Exchg Rate[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1000[/TD]

[TD="align: right"]888888[/TD]

[TD="align: right"]1000[/TD]

[TD="align: right"]100[/TD]

[TD="align: right"]1/1/2012[/TD]
[TD="align: right"]2/1/2012[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]2012[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]2000[/TD]

[TD="align: right"]488080[/TD]

[TD="align: right"]2000[/TD]

[TD="align: right"]100[/TD]

[TD="align: right"]1/1/2012[/TD]
[TD="align: right"]2/1/2012[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]2012[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

To find and match each "ref key" on the "Trailer" sheet:

Trailer

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

I

IN

Case

No

*

*

*

*

CAP

CAP

I

IN

Case

No

*

*

*

*

CAP

CAP

I

IN

Case

No

*

*

*

*

CAP

CAP

I

IN

Case

No

*

*

*

*

CAP

CAP

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Ref Key[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Customer[/TD]
[TD="align: center"]Ref Type[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]Seq[/TD]
[TD="align: center"]Qty[/TD]
[TD="align: center"]UM[/TD]
[TD="align: center"]Product[/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"]Tax[/TD]
[TD="align: center"]Product Name[/TD]
[TD="align: center"]Product Description[/TD]
[TD="align: center"]Detail[/TD]
[TD="align: center"]Adjst[/TD]
[TD="align: center"]Ext Price[/TD]
[TD="align: center"]Cost[/TD]
[TD="align: center"]Return Code[/TD]
[TD="align: center"]Ship Loc[/TD]
[TD="align: center"]Sales Loc[/TD]
[TD="align: center"]Agent[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1000[/TD]

[TD="align: right"]888888[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]10001[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"]88888[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]1000[/TD]

[TD="align: right"]888888[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]10002[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"]88888[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]2000[/TD]

[TD="align: right"]488080[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]10001[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"]88888[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]2000[/TD]

[TD="align: right"]488080[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]10002[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"]88888[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

And create a new sheet called "Results" that looks like this. So, its saying for each header record "ref key" (ex: 1000) find each trailer record "ref key" with the same number (1000) and then copy the header row exactly and put the each matching trailer row under it.

Results

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

H

IN

*

*

Invoice

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

Yes

I

IN

Case

No

*

*

*

*

CAP

CAP

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

I

IN

Case

No

*

*

*

*

CAP

CAP

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

H

IN

*

*

Invoice

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

Yes

I

IN

Case

No

*

*

*

*

CAP

CAP

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

I

IN

Case

No

*

*

*

*

CAP

CAP

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]1000[/TD]

[TD="align: right"]888888[/TD]

[TD="align: right"]1000[/TD]

[TD="align: right"]100[/TD]

[TD="align: right"]1/1/2012[/TD]
[TD="align: right"]2/1/2012[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]2012[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1000[/TD]

[TD="align: right"]888888[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]10001[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"]88888[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]1000[/TD]

[TD="align: right"]888888[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]10002[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"]88888[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]2000[/TD]

[TD="align: right"]488080[/TD]

[TD="align: right"]2000[/TD]

[TD="align: right"]100[/TD]

[TD="align: right"]1/1/2012[/TD]
[TD="align: right"]2/1/2012[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]2012[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]2000[/TD]

[TD="align: right"]488080[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]10001[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"]88888[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]2000[/TD]

[TD="align: right"]488080[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]10002[/TD]
[TD="align: right"]100[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]

[TD="align: right"]88888[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

The columns have to remain as is. The program I'm importing this data with is looking for a certain amount of columns. Thanks again.

 

[patel45]

from FAQ

• Make sure you are in the correct forum for the question you are asking.
• Word your questions clearly.
• ALWAYS state what version of Excel and Windows you are using (i.e., Excel 97, Windows XP, etc.)
• Don't use a subject line like HELP ME! or EXCEL question. Instead, say something like Trouble With Pivot Tables In XL97. And remember, saying URGENT doesn't make it so to anyone but you.
• Provide background information for your question when needed. Include example data and formulas in your post. The more you tell us up front, the easier it is for us to answer your questions. If your formulas aren't giving the results you want, include the results you want to get, as well as the results you're getting. Your post should include at least:

 

[steveo275]

My sincerest apologies to the board for not reading the FAQ. I'm sure this has devastated the MrExcel community. I'm not sure how I'm going to sleep at night knowing the pain my subject has caused all of you. We must put this travesty behind us and look forward. It's the only way the healing process can begin.