Sort/print order of label data

[Jen]

How do I get my labels to print down instead of across.
The data is already sorted by zip then last, first, but I
want the order they are merged on the labels to be down
instead of across.

 

[Doug Robbins - Word MVP]

Hi Jen,

If the data source is a table in a Word document, you can use the following
macro to sort it so that the labels print in that order:

' 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 = "5" ' Set default.
labelrows = InputBox(Message, Title, Default)
ActiveDocument.Tables(1).Columns.Add
BeforeColumn:=ActiveDocument.Tables(1).Columns(1)
ActiveDocument.Tables(1).Rows(1).Range.Cut
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

If the data source is not a table in a Word document, you can get it into
that format by use of a catalog or directory type mailmerge with the merge
fields in the cells of a one row table, execute that merge, then insert a
row at the top of the table to hold the field names and then run the macro.

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Jen]

Hi Doug

Thank you very much for your response, it helped me to create the following expression in Access

Sort: (Int(([ID]-1)/([Number of Label Rows:]*[Number of Label Columns:]))*[Number of Label Rows:])+(([ID]-1) Mod [Number of Label Rows:]

To use this, the ID field is an autonumber, 1-? that corresponds with the way you want the data sorted in the columns, ie: zip, last, first. It prompts you for the number of rows and columns for the labels. When you order the records by Sort, then ID, they are in the correct order so that they print down instead of across in a label mail merge

Thanks

jen

 

[Doug Robbins - Word MVP]

Hi Jen,

Thanks for posting back with that. It's good to have another way.

However, if you have the data in Access, it has a Label Wizard (under
Reports) and then with the Report in Design View, there is an option in the
File>Page Setup>Columns to set the order to either Down then Across or
Across and then Down.

[Sound of head knocking against wall]

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

Hi Doug,

Thank you very much for your response, it helped me to create the

following expression in Access:

Sort: (Int(([ID]-1)/([Number of Label Rows:]*[Number of Label

Columns:]))*[Number of Label Rows:])+(([ID]-1) Mod [Number of Label Rows:])

To use this, the ID field is an autonumber, 1-? that corresponds with the

way you want the data sorted in the columns, ie: zip, last, first. It
prompts you for the number of rows and columns for the labels. When you
order the records by Sort, then ID, they are in the correct order so that
they print down instead of across in a label mail merge.