Macro Help: 6 Sets of Addresses in single row --> Export to new workbook, each address on a new sheet preceded by the data in columns A-D

[jeffcoleky]

Example files, click to download:
1. ExportMe.xlsm
2. Exported.xlsm

My post is quite long but don't be intimidated by it. The macro I need hopefully will be fairly easy for someone here, I just couldn't figure it out.

Objective: I'm trying to export several rows of data to a new worksheet. Each person/row has 6 addresses and I want to split the address types into new sheets in the same file. I also need the first 4 four cells of each line to be repeated prior to the address.

A step-by-step explanation:

1. User selects ANY cell range within "c:\dropbox\ExportMe.xlsm"
2. For each row of selected data, macro copies the following data into the first 8 columns of a new workbook "c:\dropbox\Exported.xlsm", and in their own respective sheet.

Sheet 1
a. Column A
b. Column B
c. Column C
d. Column D
e. Column E
f. Column F
g. Column G
h. Column H
Sheet 2
a. Column A
b. Column B
c. Column C
d. Column D
e. Column I
f. Column J
g. Column K
h. Column L
Sheet 3
a. Column A
b. Column B
c. Column C
d. Column D
e. Column M
f. Column N
g. Column O
h. Column P
Sheet 4
a. Column A
b. Column B
c. Column C
d. Column D
e. Column Q
f. Column R
g. Column S
h. Column T
Sheet 5
a. Column A
b. Column B
c. Column C
d. Column D
e. Column U
f. Column V
g. Column W
h. Column X
Sheet 6
a. Column A
b. Column B
c. Column C
d. Column D
e. Column Y
f. Column Z
g. Column AA
h. Column AB

BEFORE:
1. ExportMe.xlsm - Excel 2010

	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
Primary Address	Second Address	Third Address	Fourth Address	Fifth Address	Sixth Address
First Name	Last Name	Field3	Value	Street	City	State	Zip	Street	City	State	Zip	Street	City	State	Zip	Street	City	State	Zip	Street	City	State	Zip	Street	City	State	Zip
Brian	Way	Yes	12116 Saint Clair Dr	Mooresville	NC	2053 JOHNSON RD	LOUISVILLE	KY	5406 Gaskin Ct	Louisville	KY	10421 Martinside Dr	Mooresville	NC	10719 Allen Dr	Fairdale	KY	1216 Lexington Rd	Louisville	KY
Andy & Pamela	Jones	No	8102 Kerry Rd	Mooresville	NC	6303 GOALBY DR	LOUISVILLE	KY	4309 Seagrape Rd	Jeffersontown	KY	4030 Mapleton Ave	Mooresville	NC	11710 Lower River Rd	Louisville	KY	5200 Benson Ct	Louisville	KY
Melyssa	Harrod	Yes	5109 Garden Green Way	Mooresville	NC	6602 AUDRY WAY UNIT 104	LOUISVILLE	KY	2124 Dogoon Dr	Louisville	KY	5504 Westhall Ave	Mooresville	NC	13927 Fancy Gap	Jeffersontown	KY	7600 Aspen Ridge Rd	Louisville	KY
Yvonne	Webb	No	815 Bluegrass Ave	Mooresville	NC				4951 S 3Rd St	Louisville	KY	7411 Arapaho Dr	Mooresville	NC	3927 Pinoak View Ct	Jeffersontown	KY	10603 Back Run Rd	Jeffersontown	KY

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

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

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

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

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

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]$110,380 [/TD]

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

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

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

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

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

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

[TD="align: center"]4[/TD]

[TD="align: right"]$89,880 [/TD]

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

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

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

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

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

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

[TD="align: center"]5[/TD]

[TD="align: right"]$242,250 [/TD]

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

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

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

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

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

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

[TD="align: center"]6[/TD]

[TD="align: right"]$100,430 [/TD]

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

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

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

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

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

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

</tbody>

Sheet1​

So for instance, if someone highlighted any range of cells within rows 3, 4, 5, 6, after the macro is run it would look like this:

AFTER
2. Exported.xlsm - Excel 2010.

	A	B	C	D	E	F	G	H
First Name	Last Name	Field3	Value	Street	City	State	Zip
Brian	Way	Yes	12116 Saint Clair Dr	Mooresville	NC
Andy & Pamela	Jones	No	8102 Kerry Rd	Mooresville	NC
Melyssa	Harrod	Yes	5109 Garden Green Way	Mooresville	NC
Yvonne	Webb	No	815 Bluegrass Ave	Mooresville	NC

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]$110,380 [/TD]

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

[TD="align: center"]3[/TD]

[TD="align: right"]$89,880 [/TD]

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

[TD="align: center"]4[/TD]

[TD="align: right"]$242,250 [/TD]

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

[TD="align: center"]5[/TD]

[TD="align: right"]$100,430 [/TD]

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

</tbody>

Sheet1​

	A	B	C	D	E	F	G	H
First Name	Last Name	Field3	Value	Street	City	State	Zip
Brian	Way	Yes	2053 JOHNSON RD	LOUISVILLE	KY
Andy & Pamela	Jones	No	6303 GOALBY DR	LOUISVILLE	KY
Melyssa	Harrod	Yes	6602 AUDRY WAY UNIT 104	LOUISVILLE	KY

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]$110,380 [/TD]

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

[TD="align: center"]3[/TD]

[TD="align: right"]$89,880 [/TD]

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

[TD="align: center"]4[/TD]

[TD="align: right"]$242,250 [/TD]

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

[TD="align: center"][/TD]

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

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

</tbody>

Sheet2​

	A	B	C	D	E	F	G	H
First Name	Last Name	Field3	Value	Street	City	State	Zip
Brian	Way	Yes	5406 Gaskin Ct	Louisville	KY
Andy & Pamela	Jones	No	4309 Seagrape Rd	Jeffersontown	KY
Melyssa	Harrod	Yes	2124 Dogoon Dr	Louisville	KY
Yvonne	Webb	No	815 BLUEGRASS AVE	LOUISVILLE	KY

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]$110,380 [/TD]

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

[TD="align: center"]3[/TD]

[TD="align: right"]$89,880 [/TD]

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

[TD="align: center"]4[/TD]

[TD="align: right"]$242,250 [/TD]

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

[TD="align: center"]5[/TD]

[TD="align: right"]$100,430 [/TD]

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

</tbody>

Sheet3​

And so forth with Sheets 4, 5, 6...

**NOTE: Sometimes there are empty address fields. If so please skip it.

Does this make sense and if so, can someone help me out with a macro please? If not, please ask and i'll do my best to clarify.

Thanks -Jeff

 

[jeffcoleky]

Was this too complicated? All i need is to know how to export specified columns to a new workbook and in separate sheets. Please help!

 

[jeffcoleky]

I found these macros which are close but i don't know how to modify them to do what I asked above:
VBA Express : Excel - Distribute Data from One Main Worksheet to Several Other Worksheets
asap utilities / excel blog » Macro: Export each column to a separate sheet
Excel MACRO to save worksheets as seperate files ?