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

Active Member
Joined
May 24, 2011
Messages
274
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

ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1Primary AddressSecond AddressThird AddressFourth AddressFifth AddressSixth Address
2First NameLast NameField3ValueStreetCityStateZipStreetCityStateZipStreetCityStateZipStreetCityStateZipStreetCityStateZipStreetCityStateZip
3BrianWayYes$110,380 12116 Saint Clair DrMooresvilleNC281172053 JOHNSON RDLOUISVILLEKY402455406 Gaskin CtLouisvilleKY4022910421 Martinside DrMooresvilleNC2811710719 Allen DrFairdaleKY401181216 Lexington RdLouisvilleKY40204
4Andy & PamelaJonesNo$89,880 8102 Kerry RdMooresvilleNC281176303 GOALBY DRLOUISVILLEKY402584309 Seagrape RdJeffersontownKY402994030 Mapleton AveMooresvilleNC2811711710 Lower River RdLouisvilleKY402075200 Benson CtLouisvilleKY40272
5MelyssaHarrodYes$242,250 5109 Garden Green WayMooresvilleNC281176602 AUDRY WAY UNIT 104LOUISVILLEKY402192124 Dogoon DrLouisvilleKY402235504 Westhall AveMooresvilleNC2811713927 Fancy GapJeffersontownKY402997600 Aspen Ridge RdLouisvilleKY40214
6YvonneWebbNo$100,430 815 Bluegrass AveMooresvilleNC281174951 S 3Rd StLouisvilleKY402147411 Arapaho DrMooresvilleNC281173927 Pinoak View CtJeffersontownKY4029910603 Back Run RdJeffersontownKY40299

<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.

ABCDEFGH
1First NameLast NameField3ValueStreetCityStateZip
2BrianWayYes$110,380 12116 Saint Clair DrMooresvilleNC28117
3Andy & PamelaJonesNo$89,880 8102 Kerry RdMooresvilleNC28117
4MelyssaHarrodYes$242,250 5109 Garden Green WayMooresvilleNC28117
5YvonneWebbNo$100,430 815 Bluegrass AveMooresvilleNC28117

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
ABCDEFGH
1First NameLast NameField3ValueStreetCityStateZip
2BrianWayYes$110,380 2053 JOHNSON RDLOUISVILLEKY40245
3Andy & PamelaJonesNo$89,880 6303 GOALBY DRLOUISVILLEKY40258
4MelyssaHarrodYes$242,250 6602 AUDRY WAY UNIT 104LOUISVILLEKY40219

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2
ABCDEFGH
1First NameLast NameField3ValueStreetCityStateZip
2BrianWayYes$110,380 5406 Gaskin CtLouisvilleKY40229
3Andy & PamelaJonesNo$89,880 4309 Seagrape RdJeffersontownKY40299
4MelyssaHarrodYes$242,250 2124 Dogoon DrLouisvilleKY40223
5YvonneWebbNo$100,430 815 BLUEGRASS AVELOUISVILLEKY40215

<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

 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

jeffcoleky

Active Member
Joined
May 24, 2011
Messages
274
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,862
Messages
5,574,711
Members
412,613
Latest member
EFRATA
Top