Good Afternoon,
I am trying to set up a list of data to do a mail merge into Word Labels.
The Current macro copies the required columns (Products) to a new Sheet(Labels). Once copied, the rows are duplicated based on the total quantity so that the correct no of labels are printed.
From here...and I am not sure where to even start, I would like to take the Size of each garment and copy n times based on the count in the column. Sheet 3 shows what the end result should look like. Workbook attached.
Thanks for your help.
Code
Sub Labels2()
Dim lastrow As Long
Dim rng As Range
lastrow = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
'Clear Labels Sheet
Sheet1.Cells.Clear
'Copy Columns
With Sheet2
.Activate
.Range("A2:C" & lastrow & ", E2:J" & lastrow).Copy Sheet1.Range("A1")
End With
Application.CutCopyMode = False
'Remove any blank rows
Sheet1.Select
N = Cells(Rows.Count, "C").End(xlUp).Row
For I = N To 1 Step -1
Set r = Cells(I, "c")
If IsEmpty(r) Then
r.EntireRow.Delete
End If
Next
'Replicate Rows based on the Value in Col I
Range("a1:i1").Copy Range("k1")
On Error Resume Next
For Each rng In Range("I2", Range("I" & Rows.Count).End(xlUp))
Cells(Rows.Count, 11).End(xlUp)(2).Resize(rng.Value, 9) = rng.Offset(, -8).Resize(1, 9).Value
Next rng
End Sub
Display More