• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 586
  • Last Modified:

WORD Mail Merge - Skip to next page based data value

I am building a mail merge document that will print instructor assignments for several instructor teams and within each team they are responsible several groups of students.   On each change in TEAM number and on each change in RUNGROUP name I would like to skip to a new printed page.

As an example, Team #1 has twenty students.  Five in each RunGroup – Green, Blue, Yellow, White.   Each printed page in the attached document has a place for three student assignments.   Therefore, the first three greens will be on the first page.  The last two greens on the second page.  Leave the third section on page two blank and skip to a new page to begin the Blue group.   Same process when the data changes from Team #1 to Team #2.

The data is in an Excel 2007 file.

Thanks,
Jerry
 EE-Mail-Merge-Sample.docx
0
Jerry Paladino
Asked:
Jerry Paladino
  • 4
  • 4
1 Solution
 
GrahamSkanCommented:
Sounds like a sort of one-to-many merge, and quite complex. Here is Microsoft's suggestion using IF fields in a simpler situation.

http://support.microsoft.com/kb/294686/

However, I think that it would be easier to use VBA in Excel to pad your data out with blank students.
0
 
Jerry PaladinoAuthor Commented:
Graham,
A sample data file is attached.   With this added information do you still suggest an Excel VBA solution to pad the data as the best option?   Would that be easier than a WORD VBA solution?

Thanks,
Jerry

EE-Master-Roster.xlsx
0
 
GrahamSkanCommented:
The logic of a VBA solution would be the same in Excel or Word. However in Word, under VBA control, it would produce an output document (or a print job) for every three records, rather than a single multi-section and mult-ipage document, or single print job that is normally expected.

It's getting late here, but I'll try some experiments over the weekend, and post any outcome. Feel free add a prompting comment if I seem to have forgotten.
0
Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

 
Jerry PaladinoAuthor Commented:
Sounds like the Excel option is the way to go. This will be turned over to an Admin person who will need the single file/print solution.  I think the multi-documents would be confusing for them.   I'll leave this open for a while and see if you come up with something you want to post.  

Thank You
Jerry
0
 
Jerry PaladinoAuthor Commented:
Hi Graham,

Is the Excel macro something you want to tackle or would it be better for me to close this one and post something in the Excel zone?  Either way is fine with me.

I ran a few manual samples with padding with blank rows and found that checking team numbers is not necessary.  Padding the records by RunGroup(color) with blanks to keep them as a multiple of three the team numbers automatically start on a new page without any intervention or checking.

Let me know,
Thanks,
Jerry
0
 
GrahamSkanCommented:
Sorry for the delay. I had lost track of this question. I'll have another look now. If I'm not able to get back in the next two hours, then take it that I haven't been able to come up with anything.
0
 
GrahamSkanCommented:
See if this does what you need:
Sub PadToThrees()
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim r1 As Integer
    Dim r2 As Integer
    Dim r As Integer
    Dim strColour As String
    
    Application.ScreenUpdating = False
    Set sh1 = ActiveWorkbook.Worksheets("Roster Info")
    Set sh2 = ActiveWorkbook.Worksheets("Sheet1")
    sh1.Rows(1).Copy
    sh2.Paste sh2.Rows(1)
    r1 = 2
    r2 = 2
    strColour = sh1.Cells(r1, 8)
    Do While strColour <> ""
        Do While strColour = sh1.Cells(r1, 8).Value
            sh1.Rows(r1).Copy
            sh2.Paste sh2.Rows(r2)
            r1 = r1 + 1
            r2 = r2 + 1
        Loop
        r2 = r2 + 2 - (r2 Mod 3)
        strColour = sh1.Cells(r1, 8).Value
    Loop
    Application.ScreenUpdating = True
End Sub

Open in new window

0
 
Jerry PaladinoAuthor Commented:
Works perfect Graham.  Thank You!
Jerry
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now