Hi Glenn,
Another possibility by adding another column to you data for how
many passes you want to print for a row. I assume that it would
be Column 2 (Col B), but you will get a choice of changing the
column. You can use the shorter macro to invoke the larger one
with a specific value which is set up for column 4 as an example.
The number you place in col B or other column of your choice
will be changed to a 1 in the original row and it's replications
on the new sheet.
Everything on the new sheet will be left undisturbed except where
you have a number greater than 1 in your selected column.
So if you have an empty cell in your number column, you will
still present one line to mail merge.
Because the cells with a number in the column
Option Explicit
Sub repeat_BxN_Col4()
repeat_BxN (4)
End Sub
Sub repeat_BxN(Optional ColB As Long)
'Repeat row x Col B value 2003-08-22 in misc
Dim wsSource As Worksheet
Dim wsNew As Worksheet
Set wsSource = ActiveSheet
Dim rng As Range, rng2 As Range
Dim vRows As Long, colBstr As String
Dim I As Long
If ColB = 0 Then
On Error Resume Next
ColB = InputBox("Which column has Repetition Count", _
"Repetition", 2)
If Err.number <> 0 Then Exit Sub
On Error GoTo 0
End If
Sheets(ActiveSheet.Name).Copy _
Before:=Sheets(ActiveSheet.Name)
Set wsNew = ActiveSheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set rng = Cells.Columns(ColB) _
.SpecialCells(xlCellTypeConstants, 1)
Dim A As Long
For A = rng.Areas.Count To 1 Step -1
For I = rng.Areas(A).Count To 1 Step -1
Set rng2 = rng.Areas(A)(I).EntireRow
vRows = rng2.Cells(1, ColB).Value
rng2.Cells(1, ColB).Value = 1
If vRows > 1 Then
rng2.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows - 1).Insert Shift:=xlDown
rng2.AutoFill rng2.Resize( _
rowsize:=vRows), xlFillCopy
End If
Next I
Next A
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
If not familiar with macros, directions to install and use on my
Getting Started with Macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Page on printing labels with Mail Merge is mailmerg.htm
BTW,
With Lance's example, I had a bit of diffulty figuring out what was being
asked for. But you would enter something like
A3:J4 for first question, essentially for rows 3 through 4
B3:B4 for the second question, essentially to indicate the 2nd column,
for the numbers of rows to repeat for the first range.
i.e. A3:J3 repeated B3 times, and
A4:J4 repeated B4 times
Since I can't remember what I entered first, at leat it might be helpful
to show what the first choice was. Kind of redundant I think you just want the rows
for the first and the column for the second. Or maybe I missed something. The
following would at least tell you what you entered for the first part.
Set rng2 = Application.InputBox(Prompt:="Number of passes Range " _
& Chr(10) & "col 2 of " & rng.Address(0, 0), Type:=8)
One additional comment, Mail Merge requires column labels at the
top of the columns for describing the fields used in Mail Merge.
