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
1					Primary Address				Second Address				Third Address				Fourth Address				Fifth Address				Sixth Address
2	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
3	Brian	Way	Yes	$110,380	12116 Saint Clair Dr	Mooresville	NC	28117	2053 JOHNSON RD	LOUISVILLE	KY	40245	5406 Gaskin Ct	Louisville	KY	40229	10421 Martinside Dr	Mooresville	NC	28117	10719 Allen Dr	Fairdale	KY	40118	1216 Lexington Rd	Louisville	KY	40204
4	Andy & Pamela	Jones	No	$89,880	8102 Kerry Rd	Mooresville	NC	28117	6303 GOALBY DR	LOUISVILLE	KY	40258	4309 Seagrape Rd	Jeffersontown	KY	40299	4030 Mapleton Ave	Mooresville	NC	28117	11710 Lower River Rd	Louisville	KY	40207	5200 Benson Ct	Louisville	KY	40272
5	Melyssa	Harrod	Yes	$242,250	5109 Garden Green Way	Mooresville	NC	28117	6602 AUDRY WAY UNIT 104	LOUISVILLE	KY	40219	2124 Dogoon Dr	Louisville	KY	40223	5504 Westhall Ave	Mooresville	NC	28117	13927 Fancy Gap	Jeffersontown	KY	40299	7600 Aspen Ridge Rd	Louisville	KY	40214
6	Yvonne	Webb	No	$100,430	815 Bluegrass Ave	Mooresville	NC	28117					4951 S 3Rd St	Louisville	KY	40214	7411 Arapaho Dr	Mooresville	NC	28117	3927 Pinoak View Ct	Jeffersontown	KY	40299	10603 Back Run Rd	Jeffersontown	KY	40299

<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>
</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
1	First Name	Last Name	Field3	Value	Street	City	State	Zip
2	Brian	Way	Yes	$110,380	12116 Saint Clair Dr	Mooresville	NC	28117
3	Andy & Pamela	Jones	No	$89,880	8102 Kerry Rd	Mooresville	NC	28117
4	Melyssa	Harrod	Yes	$242,250	5109 Garden Green Way	Mooresville	NC	28117
5	Yvonne	Webb	No	$100,430	815 Bluegrass Ave	Mooresville	NC	28117

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

	A	B	C	D	E	F	G	H
1	First Name	Last Name	Field3	Value	Street	City	State	Zip
2	Brian	Way	Yes	$110,380	2053 JOHNSON RD	LOUISVILLE	KY	40245
3	Andy & Pamela	Jones	No	$89,880	6303 GOALBY DR	LOUISVILLE	KY	40258
4	Melyssa	Harrod	Yes	$242,250	6602 AUDRY WAY UNIT 104	LOUISVILLE	KY	40219

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

	A	B	C	D	E	F	G	H
1	First Name	Last Name	Field3	Value	Street	City	State	Zip
2	Brian	Way	Yes	$110,380	5406 Gaskin Ct	Louisville	KY	40229
3	Andy & Pamela	Jones	No	$89,880	4309 Seagrape Rd	Jeffersontown	KY	40299
4	Melyssa	Harrod	Yes	$242,250	2124 Dogoon Dr	Louisville	KY	40223
5	Yvonne	Webb	No	$100,430	815 BLUEGRASS AVE	LOUISVILLE	KY	40215

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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 ?