Hi I need to generate quite a few distinct "notices" (like more than 1,000 or
so). The contents of the notices are already in excel cells, arranged as
follows: each row contains the notice number, the message on the front of the
notice, and information for the back of the notice (in separate columns).

I want to create the notices using a "mail merge" with word (its the only
way I can think of to do it) - however, I'll be doing more than one record
per page (I think it will work out to six records per page based on the
current size of the notice) - So... since I'll be printing duplex (front &
back of the paper) the fields for the "front" of record 1 - 6 need to be
merged first, then the "back" of records 1-6 need to be merged on page 2 of
the word document - up to this point no problem, but here we go: I've got the
notices laid out in word in a table like follows:

notice 1 notice 2
notice 3 notice 4
notice 5 notice 6

Now, since i'm going to print these "duplex" so that I can cut out the
notices, you'll see that I must have the "back" (page 2, or all the even
pages) arranged as follows:

notice 2 notice 1
notice 4 notice 3
notice 6 notice 5

So, my idea is to create a macro that loops through the rows and arranges
the information in the following order:

notice 1 - front info
notice 2 - front info
notice 3 - front info
notice 4 - front info
notice 5 - front info
notice 6 - front info
notice 2 - back info
notice 1 - back info
notice 4 - back info
notice 3 - back info
notice 6 - back info
notice 5 - back info

I understand how to loop through rows, insert rows, copy data, etc. I've
recorded the macro for opening up word and creating a mail merge from
excel... but I'm stumped on how to get the rows arranged properly. Right
now, the macro creates a new worksheet, and transfers the data from the
"Notices" worksheet to the "Merge" worksheet. Anyone have any ideas on how
to make this work? Especially if the number of rows I have is NOT divisible
by 6 (like 1034 rows)?

If you can't bring Mohamad to the mountain, bring the mountain to Mohamad!!!

I think you are making the problem much harder than required. In the merge
macro count the number of pages. If the pages for a notice is ODD add a page
break.

"JeffC" wrote:

Hi I need to generate quite a few distinct "notices" (like more than 1,000 or
so). The contents of the notices are already in excel cells, arranged as
follows: each row contains the notice number, the message on the front of the
notice, and information for the back of the notice (in separate columns).

I want to create the notices using a "mail merge" with word (its the only
way I can think of to do it) - however, I'll be doing more than one record
per page (I think it will work out to six records per page based on the
current size of the notice) - So... since I'll be printing duplex (front &
back of the paper) the fields for the "front" of record 1 - 6 need to be
merged first, then the "back" of records 1-6 need to be merged on page 2 of
the word document - up to this point no problem, but here we go: I've got the
notices laid out in word in a table like follows:

notice 1 notice 2
notice 3 notice 4
notice 5 notice 6

Now, since i'm going to print these "duplex" so that I can cut out the
notices, you'll see that I must have the "back" (page 2, or all the even
pages) arranged as follows:

notice 2 notice 1
notice 4 notice 3
notice 6 notice 5

So, my idea is to create a macro that loops through the rows and arranges
the information in the following order:

notice 1 - front info
notice 2 - front info
notice 3 - front info
notice 4 - front info
notice 5 - front info
notice 6 - front info
notice 2 - back info
notice 1 - back info
notice 4 - back info
notice 3 - back info
notice 6 - back info
notice 5 - back info

I understand how to loop through rows, insert rows, copy data, etc. I've
recorded the macro for opening up word and creating a mail merge from
excel... but I'm stumped on how to get the rows arranged properly. Right
now, the macro creates a new worksheet, and transfers the data from the
"Notices" worksheet to the "Merge" worksheet. Anyone have any ideas on how
to make this work? Especially if the number of rows I have is NOT divisible
by 6 (like 1034 rows)?