EXCEL1: Macro to copy from Excel to Word

[aacod]

I have different Addresses in Cells Q4 : Q180. (Format same as below)

e.g John Smith
98 South Hampton Rd.
Town
State 00000-0000

I want a Macro to copy the address data from cells Q4:Q180, paste it into a word document WITOUT the Gridlines, to print on Avery Address label sheet # 18160, size 1" x 2-5/8" (30 labels per sheet) at a later date when necessary.

Thanks.

 

[Tinbendr]

Name the range, then use Word's Merge function. Easy, peasy.

Don't try and reinvent the wheel!

 

[aacod]

Tinbendr,

I tried it, and since the format is as follows:

John Smith
98 South Hampton Rd.
Town
State 00000-0000

The labels donot print right. Either I have to create a new database splitting by first name, last name etc, which would be very tedious. OR would appreciate if you can provide a macro to break the above format with each data field in seperate column. I can use Text to Column but the format of fieldis not the same in all addresses. Some have spaces and otheres "," comma

Thanks.

 

[aacod]

I have addresses on 26 sheets. Each sheet is labelled A to Z.

I want to automate printing with a macro so that when addresses on sheet A is done, the macro automatically starts to print from sheet B and so on.

Names in column A.

Addresses in column E.

Thanks in advance.

aacod.

 

[Tinbendr]

So, the full name is in A?
Street address, City, State, Zip in E?

Copy and paste a sample right into the message box here on the board so I can see what you're describing.

I'm thinking we could create a single temporary sheet with all the names on it, and run the merge using that.

 

[Macropod]

... since the format is as follows:

John Smith
98 South Hampton Rd.
Town
State 00000-0000

The labels donot print right.

What particular problem are you having? Simply saying the format isn't 'right' is not conducive to a solution.

 

[aacod]

OK, the current format is as follows in 26 sheets:

Column A:

Mr. John & Mrs. June Doe

Column E:

567 XXXXXXXXX Rd, TTTTTTTT, PA 78954

Result required as follows:

Mr. John & Mrs. June Doe
567 XXXXXXXXX Rd
TTTTTTTT, PA 78954.

Thanks.

aacod

 

[Macropod]

Your last post implies the data are not, in fact, formatted in Excel as you've previously told us they are. Look at your first and second posts - they clearly imply there are line breaks in the address in Excel. Your latest post implies there are no line breaks.

A mailmerge cannot re-format the data for you. You should have separate columns in Excel for the street/box address (two columns are required for some addresses), locality, State and Zip. No macro or formula is likely to be able to fix this for you. You could use Text-to-Columns, on the addresses, using the comma as the field separator, but you're going to have to manually clean the data up afterwards (you can probably get by without separating the State & Zip).

 

[aacod]

Macropod,

Sorry for the confusion, for now let's consider the 26 sheets.

I will have to use Text to column function individually on all 26 sheets which would be tedious, so I want a macro to run on Column E for the text to column function on all 26 sheets labelled A to Z and then print on Avery labels directly from EXCEL instead of mail merge from word document.

Column A:

Mr. John & Mrs. June Doe.

Column E: Column F Column G

567 XXXXXXXXX Rd. TTTTTTTT PA 78954

Result required as follows on Avery labels(30 labels on a sheet):

Mr. John & Mrs. June Doe.
567 XXXXXXXXX Rd.
TTTTTTTT, PA 78954.


Is that possible?

Thanks.

 

[Macropod]

Before you can do a mailmerge, all the data will have to be on one worksheet, because that's all a mailmerge will work with. So doing the text-to-columns thing on 26 sheets really isn't the issue. Since you need to get the data onto one sheet, only one text-to-columns process is required. Having done that, though, you will still have to go through all the data to get them alinged properly. And that's where the real work is going to be.