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
'------------------------------------------------------
