[hugo901]

How do I get Word to add multiple labels in the merged label file based on a
variable in a cell in the data list? Say the value in the column "Quantity"
of a row is 20. I'd like 40 labels for that row of data. If the value is 1,
I'd like 2 labels.

Thank you,
Hugo

[Peter Jamieson]

What is the data source? Excel? Access table?...

--
Peter Jamieson
http://tips.pjmsn.me.uk

"hugo901" wrote in message
...
How do I get Word to add multiple labels in the merged label file based on
a
variable in a cell in the data list? Say the value in the column
"Quantity"
of a row is 20. I'd like 40 labels for that row of data. If the value is
1,
I'd like 2 labels.

Thank you,
Hugo

[hugo901]

Excel.

"Peter Jamieson" wrote:

What is the data source? Excel? Access table?...

--
Peter Jamieson
http://tips.pjmsn.me.uk

"hugo901" wrote in message
...
How do I get Word to add multiple labels in the merged label file based on
a
variable in a cell in the data list? Say the value in the column
"Quantity"
of a row is 20. I'd like 40 labels for that row of data. If the value is
1,
I'd like 2 labels.

Thank you,
Hugo

[Peter Jamieson]

One way is to run an Excel macro on a copy of your sheet - someone posted
the code below a while back )(well, actually, this is their version with
oding suggestions by me, such as they are, so not well tested)...

'------------------------------------------------------
Sub RepeatMailingLabels()


' Using constants makes it easier to modify the sheets you want to use
' But there are other ways to parameterise this, for example using workbook
and worksheet names


Const sourceSheet = 1 ' the sheet number containing the source data
Const targetSheet = 2 ' the sheet number that will contain the label data
Const countColumn = 1 ' the column in sourceSheet that contains the label
count


' Let's try to declare every variable we use


Dim c As Integer
Dim r As Long
Dim lDestStartRow As Long
Dim lDestRow As Long


' Let's put "Excel." in front of Excel objects. That way, we have a much
better
' chance of using this code even in Word VBA


Dim wsSource As Excel.Worksheet
Dim wsTarget As Excel.Worksheet
Dim mbrAnswer As VbMsgBoxResult
Dim rng2Copy As Excel.Range
Set wsSource = Excel.ActiveWorkbook.Sheets(sourceSheet)
Set wsTarget = Excel.ActiveWorkbook.Sheets(targetSheet)


If ActiveWorkbook.Sheets.Count 2 Then


' Spell it out! The clearer the better.


MsgBox "Your Workbook must have at least two Sheets. The first sheet is
assumed to be the source of the data, and column one contains the label
count. The second sheet will be overwritten by the results.", vbCritical,
"Sheet Count"
Exit Sub
Else
mbrAnswer = MsgBox("This macro will delete all information on the second
sheet in your workbook: '" & UCase(wsTarget.Name) & "'" & vbCr & vbCr & "Do
you want to proceed?", vbQuestion + vbYesNo, "Run Label Maker")
If mbrAnswer = vbYes Then


' Clear everything in the target worksheet
wsTarget.Cells.Clear
Else
Exit Sub
End If
End If


' Copy the first row


Set rng2Copy = wsSource.Cells(1, 1).CurrentRegion
For c = 1 To rng2Copy.Columns.Count
wsTarget.Cells(1, c) = wsSource.Cells(1, c)
Next c


' set up the starting row in the target


lDestStartRow = 2


' for each row in the source...


For r = 2 To rng2Copy.Rows.Count


....make the number of copies in the target specified in the appropriate
column
For lDestRow = lDestStartRow To lDestStartRow + wsSource.Cells(r,
countColumn) - 1
For c = 1 To rng2Copy.Columns.Count
wsTarget.Cells(lDestRow, c) = wsSource.Cells(r, c)
Next
Next


' remember where to start the next set of copies in the target
lDestStartRow = lDestStartRow + wsSource.Cells(r, countColumn)
Next


' It's good programming practice to release objects that we
' set up


Set wsTarget = Nothing
Set wsSource = Nothing
End Sub

'------------------------------------------------------



--
Peter Jamieson
http://tips.pjmsn.me.uk

"hugo901" wrote in message
...
Excel.

"Peter Jamieson" wrote:

What is the data source? Excel? Access table?...

--
Peter Jamieson
http://tips.pjmsn.me.uk

"hugo901" wrote in message
...
How do I get Word to add multiple labels in the merged label file based
on
a
variable in a cell in the data list? Say the value in the column
"Quantity"
of a row is 20. I'd like 40 labels for that row of data. If the value
is
1,
I'd like 2 labels.

Thank you,
Hugo