[TechnoMom]

I am using Word Mail Merge to print labels for use as a school directory.
The data is being selected out of an Excel spreadsheet. Each page has 2
columns of name/address information. I would like the labels to print
vertically, like this:

Student A Student D
Student B Student E
Student C Student F

rather than this, which is what Word does by default.
Student A Student B
Student C Student D
Student E Student F

Is this possible?
Thanks!

[Graham Mayor]

You can only do this by sorting the data so that it appears in the correct
finished order. Fellow MVP Doug Robbins produced a macro to do this with the
original Data source saved as a Word table

Sub SortData()
' Macro to assign numbers to data source so that it can be sorted to cause
'labels to print down columns
Dim Message, Title, Default, labelrows, labelcolumns, _
i As Integer, j As Integer, k As Integer
Message = "Enter the number of labels in a row" ' Set prompt.
Title = "Labels per Row" ' Set title.
Default = "3" ' Set default.
' Display message, title, and default value.
labelcolumns = InputBox(Message, Title, Default)
Message = "Enter the number of labels in a column" ' Set prompt.
Title = "Labels per column" ' Set title.
Default = "8" ' Set default.
labelrows = InputBox(Message, Title, Default)
With ActiveDocument.Tables(1)
..Columns.Add BeforeColumn:=ActiveDocument.Tables(1).Columns(1)
..Rows(1).Range.Cut
End With
k = 1
For i = 1 To ActiveDocument.Tables(1).Rows.Count - labelcolumns
For j = 1 To labelrows
ActiveDocument.Tables(1).Cell(i, 1).Range.InsertBefore _
k + (j - 1) * labelcolumns
i = i + 1
Next j
k = k + 1
i = i - 1
If k Mod labelcolumns = 1 Then k = k - labelcolumns + _
labelcolumns * labelrows
Next i
ActiveDocument.Tables(1).Sort FieldNumber:="Column 1"
ActiveDocument.Tables(1).Rows(1).Select
Selection.Paste
ActiveDocument.Tables(1).Columns(1).Delete
End Sub


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


TechnoMom wrote:
I am using Word Mail Merge to print labels for use as a school
directory. The data is being selected out of an Excel spreadsheet.
Each page has 2 columns of name/address information. I would like
the labels to print vertically, like this:

Student A Student D
Student B Student E
Student C Student F

rather than this, which is what Word does by default.
Student A Student B
Student C Student D
Student E Student F

Is this possible?
Thanks!

[ErikaC]

I copied and pasted the macro below but receive a syntax error at the line "..Columns.Add BeforeColumn:=ActiveDocument.Tables(1).Columns(1)"

Since I don't know how to write this kind of code, I don't know how to correct the syntax. I am using Word 2007.

Originally Posted by Graham Mayor

You can only do this by sorting the data so that it appears in the correct
finished order. Fellow MVP Doug Robbins produced a macro to do this with the
original Data source saved as a Word table

Sub SortData()
' Macro to assign numbers to data source so that it can be sorted to cause
'labels to print down columns
Dim Message, Title, Default, labelrows, labelcolumns, _
i As Integer, j As Integer, k As Integer
Message = "Enter the number of labels in a row" ' Set prompt.
Title = "Labels per Row" ' Set title.
Default = "3" ' Set default.
' Display message, title, and default value.
labelcolumns = InputBox(Message, Title, Default)
Message = "Enter the number of labels in a column" ' Set prompt.
Title = "Labels per column" ' Set title.
Default = "8" ' Set default.
labelrows = InputBox(Message, Title, Default)
With ActiveDocument.Tables(1)
..Columns.Add BeforeColumn:=ActiveDocument.Tables(1).Columns(1)
..Rows(1).Range.Cut
End With
k = 1
For i = 1 To ActiveDocument.Tables(1).Rows.Count - labelcolumns
For j = 1 To labelrows
ActiveDocument.Tables(1).Cell(i, 1).Range.InsertBefore _
k + (j - 1) * labelcolumns
i = i + 1
Next j
k = k + 1
i = i - 1
If k Mod labelcolumns = 1 Then k = k - labelcolumns + _
labelcolumns * labelrows
Next i
ActiveDocument.Tables(1).Sort FieldNumber:="Column 1"
ActiveDocument.Tables(1).Rows(1).Select
Selection.Paste
ActiveDocument.Tables(1).Columns(1).Delete
End Sub


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


TechnoMom wrote:
I am using Word Mail Merge to print labels for use as a school
directory. The data is being selected out of an Excel spreadsheet.
Each page has 2 columns of name/address information. I would like
the labels to print vertically, like this:

Student A Student D
Student B Student E
Student C Student F

rather than this, which is what Word does by default.
Student A Student B
Student C Student D
Student E Student F

Is this possible?
Thanks!