Automatically Insert Page Break when Field Changes during Merge

[beginner145]

I've had help from users on this forum ion the past in connection with this topic
http://www.vbaexpress.com/forum/showthread.php?51249-MAIL-MERGE-How-to-shade-rows-in-a-table-if-there-are-entries-with-same-Ref-No

My query is in connection with that topic.

I would like to know if we were to merge the whole data file into the template at once, is there an automatic way to then split/page break each time the POLLING_DISTRICT changes. For example, the first 100 or so entries could all be POLLING_DISTRICT value AA01, then the next POLLING_DISTRICT value is AA02 etc. Then force AA02 entries to start on a new page.

Or automatically split/save each POLLING_DISTRICT as a new file and automatically giving it the filename of the POLLING_DISTRICT value?

Hope this makes sense.

Thanks

[gmayor]

This is a many to one merge - you could consider http://www.gmayor.com/ManyToOne.htm which will create separate documents for each polling district or you could explore Paul Edstein's mail merge tutorial in Catalogue Mailmerge.zip which you can download from http://www.gmayor.com/downloads.htm

[beginner145]

Thanks for your reply. I did find those links, but wasn't sure if I would be allowed or be able to download addins etc at work.

I tried adapting an IF statement as follows (I'm a bit of a beginner):
{ If { MERGESEQ } = "1" "{ MERGEFIELD POLLING_DISTRICT }¶
" ""}{ SET Place1 { MERGEFIELD POLLING_DISTRICT }}¶
{ If { Place2 } <> { Place1 }"¶
----------------------------Page Break--------------------------------
{ MERGEFIELD POLLING_DISTRICT }¶
¶
{ MERGEFIELD FULL_NAME } { MERGEFIELD ELECTOR_ID }" "{ MERGEFIELD HOUSE_ID } " }{ SET Place2 { MERGEFIELD POLLING_DISTRICT }}¶

I have inserted a real page break into the statement. When merged it shows MERGESEQ and doesn't put the page breaks in. I'm assuming my coding isn't correct, but not sure where I'm going wrong.

[beginner145]

Could the current coding which is used to merge and shade cells in the table be updated to split/save as the merge is taking place?

Sub TableShader()
Application.ScreenUpdating = False
Dim Tbl As Table, oCell As Cell, bShd As Boolean, Rng As Range, StrTxt As String
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
For Each Tbl In ActiveDocument.Tables
StrTxt = "": bShd = False
With Tbl.Range
For Each oCell In .Cells
With oCell
If .ColumnIndex = 1 Then
Set Rng = .Range
With Rng
.End = .End - 1
.Start = .Words.Last.Start
End With
If Rng.Text = StrTxt Then
bShd = True
Else
bShd = False
StrTxt = Rng.Text
End If
End If
If Not IsNumeric(Rng.Text) Then bShd = False
If bShd = True Then
If .ColumnIndex = 2 Then
.Shading.BackgroundPatternColor = RGB(255, 234, 218)
On Error Resume Next
Tbl.Cell(.RowIndex - 2, .ColumnIndex).Shading.BackgroundPatternColor = RGB(255, 234, 218)
On Error GoTo 0
End If
End If
If .ColumnIndex = 3 Then
Set Rng = .Range
With Rng
.End = .End - 1
End With
Select Case Rng.Text
Case "HEF": .Shading.BackgroundPatternColor = RGB(235, 241, 222)
Case "ITR": .Shading.BackgroundPatternColor = RGB(230, 224, 236)
Case "ITR-NR": .Shading.BackgroundPatternColor = RGB(216, 237, 242)
End Select
End If
End With
Next
End With
Next
Application.ScreenUpdating = True
End Sub

[beginner145]

Hi - think I'm getting there with the coding, slowly but surely.

I've got it into the table in the word template and it is merging in, but not splitting the table when the polling district value changes.

I've attached the code, is there anyone that could have a look at it to see where I'm going wrong?

Really appreciating all the help/advice/assistance. 14492

When trying my code out in a document without a table it does seem to work and insert the page break when the polling district changes (although it is putting a page break in straight away and starting the merge on the 2nd page of the document.

However, when I use it in the template with the table it doesn't insert a page break.

[beginner145]

Here is the coding I'm using in the table. I still can't get it to insert a page break when the POLLING_DISTRICT changes:
{QUOTE{IF{MERGESEQ}=1{Set Key ""}}"{IF{MERGEFIELD POLLING_DISTRICT}<>{REF Key \* MERGEFORMAT } "{IF{MERGESEQ}> 1 ""}(THEN I'VE INSERTED A PAGE BREAK){SET Key{MERGEFIELD POLLING_DISTRICT}}" }{ MERGEFIELD "POLLING_DISTRICT" }
{MERGEFIELD "FULL_NAME" }
ELECTOR ID - { MERGEFIELD ELECTOR_ID }
HOUSE ID - { MERGEFIELD "HOUSE_ID" }