Sticker

[JERRY89]

Dear Hans,

Previously i have a macro guided by you, i want to print based on column I(Sheet Master Data) but there is a situation where i will having multiple duplicate Length number can the macro exclude the duplicate and remain the below Macro function to choose how many sticker i wanted to print. I have attached a sample as above

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

[HansV]

I don't understand - in the workbook that you have attached, the Barcode Sticker sheet consists of a single merged cell. The result will look terrible.

[JERRY89]

Hi Hans,

Perhaps the above file can let you have a clearer picture. I need the sticker to generate only the Length on the Barcode sticker sheet.

[HansV]

Thanks. You didn't even bother to adjust the code to copy the length...
Will the data always be sorted on length (column I)?
If not, would it be OK if the code sorts it on length?

[JERRY89]

Hi Hans,

I am sorry about that , i have trying for this but having error for it,i am ok if this is not sorted just generate out will worked for my worksheet

Code: Select all

Option Explicit

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
    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
    For r = 2 To m
        s = 20 * r - 38
        For i = 1 To 2
            w2.Range("B2:D11").Copy Destination:=w2.Range("B" & s + 10 * (i - 1))
            w2.Range("B").Value = w1.Range("I" & r).Value
        Next i
    Next r
    m = s + 19
    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

[HansV]

I'll get back to you, it's dinner time here.

[HansV]

See the attached version. I changed the layout of the second sheet.

Sticker Test.xlsm

[JERRY89]

Hi Hans,

Thanks for your help.It is exactly what I needed