I wanted to ask , is there a possibility to amend the below VBA to generate 4 sticker in a print area?
Code: Select all
Sub GenerateStickers()
Dim w1 As Worksheet
Dim w2 As Worksheet
Dim r As Long
Dim m As Long
Dim s As Long
Dim i As Long
Dim n As Long
n = Val(InputBox(Prompt:="How many copies of each sticker do you want?", Default:=1))
If n < 0 Then
Beep
Exit Sub
End If
Set w1 = Worksheets("Master Data")
Set w2 = Worksheets("Barcode Sticker")
w2.Range("12:" & w2.Rows.Count).Clear
w2.PageSetup.PrintArea = "B2:C11"
m = w1.Range("A" & w1.Rows.Count).End(xlUp).Row
s = 2
For r = 2 To m
For i = 1 To n
w2.Range("B2:D11").Copy Destination:=w2.Range("B" & s)
w2.Range("C" & s + 6).Value = w1.Range("D" & r).Value
w2.Range("B" & s + 8).Resize(2, 2).Merge Across:=True
s = s + 10
Next i
Next r
m = s - 1
For s = 12 To m Step 10
w2.HPageBreaks.Add Before:=w2.Range("A" & s)
Next s
w2.PageSetup.PrintArea = "B2:C" & m
w2.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Barcode.pdf"
End Sub
