Printing Labels Series and export it to pdf

[radonwilson]

Hello Readers ,

No. of Above Label on A4 sheet = 2

If I want to export 100 of this lables with number series(carton no. 1,2,3,4.....................)to pdf as 2 lables on 1 A4 sheet, so how can I do that?

 

[KOKOSEK]

When you create these labels on 1 page in excel (I think you do not need any help with it) and let say that 'Carton no.' will be in cells B9 and B24
then something like this:

Sub CreateLabels()
Dim cartNo As Integer
Dim labelPath As String
Dim labelName As String
labelPath = "Z:\"
For cartNo = 1 To 10 Step 2
    ActiveSheet.Range("B9").Value = cartNo
    ActiveSheet.Range("B24").Value = cartNo + 1
    labelName = "label_" & cartNo & "_&_" & cartNo + 1 & ".pdf"
    ActiveSheet.Range("A1:B30").ExportAsFixedFormat Type:=xlTypePDF, Filename:=labelPath & labelName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Next cartNo
End Sub

 

[radonwilson]

T

When you create these labels on 1 page in excel (I think you do not need any help with it) and let say that 'Carton no.' will be in cells B9 and B24
then something like this:

Sub CreateLabels()
Dim cartNo As Integer
Dim labelPath As String
Dim labelName As String
labelPath = "Z:\"
For cartNo = 1 To 10 Step 2
    ActiveSheet.Range("B9").Value = cartNo
    ActiveSheet.Range("B24").Value = cartNo + 1
    labelName = "label_" & cartNo & "_&_" & cartNo + 1 & ".pdf"
    ActiveSheet.Range("A1:B30").ExportAsFixedFormat Type:=xlTypePDF, Filename:=labelPath & labelName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Next cartNo
End Sub

Thank you for providing me the VBA code.

After Running this code, I am getting each page in separate pdf, is there a way in which I can get all the pages in a single pdf?

Although I can merge all the pdfs as one but it would be an extra step.

 

[KOKOSEK]

OK. Let say you labels layout (2 labels on 1 A4) is on Sheet named 'template'.
Then run this code:

Option Explicit
Dim ShList As Variant
Sub CreateTempSheets()
Dim cartNo As Integer
Dim labelName As String
Dim i As Integer
Dim intSheet As Integer
Dim arSheets() As String
Dim intArrayIndex As Integer
Application.ScreenUpdating = False
intArrayIndex = 0
labelName = "Z:\AllLabelsInOnePDF.pdf"
For cartNo = 1 To 100 Step 2
    Sheets("template").Select                                                             ' if your sheet with labels named differently, change its name here
    Sheets("template").Copy After:=Sheets(Worksheets.Count)        'and here
    Sheets("template (2)").Select                                                        'and here
    Sheets("template (2)").Name = "S" & cartNo                               'and here :-)
    Sheets("S" & cartNo).Range("B9").Value = cartNo
    Sheets("S" & cartNo).Range("B24").Value = cartNo + 1
    ReDim Preserve arSheets(intArrayIndex)
    arSheets(intArrayIndex) = Sheets("S" & cartNo).Name
    intArrayIndex = intArrayIndex + 1
Next cartNo
Sheets(arSheets).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=labelName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Application.DisplayAlerts = False
Sheets(arSheets).Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[radonwilson]

OK. Let say you labels layout (2 labels on 1 A4) is on Sheet named 'template'.
Then run this code:

Option Explicit
Dim ShList As Variant
Sub CreateTempSheets()
Dim cartNo As Integer
Dim labelName As String
Dim i As Integer
Dim intSheet As Integer
Dim arSheets() As String
Dim intArrayIndex As Integer
Application.ScreenUpdating = False
intArrayIndex = 0
labelName = "Z:\AllLabelsInOnePDF.pdf"
For cartNo = 1 To 100 Step 2
    Sheets("template").Select                                                             ' if your sheet with labels named differently, change its name here
    Sheets("template").Copy After:=Sheets(Worksheets.Count)        'and here
    Sheets("template (2)").Select                                                        'and here
    Sheets("template (2)").Name = "S" & cartNo                               'and here :-)
    Sheets("S" & cartNo).Range("B9").Value = cartNo
    Sheets("S" & cartNo).Range("B24").Value = cartNo + 1
    ReDim Preserve arSheets(intArrayIndex)
    arSheets(intArrayIndex) = Sheets("S" & cartNo).Name
    intArrayIndex = intArrayIndex + 1
Next cartNo
Sheets(arSheets).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=labelName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Application.DisplayAlerts = False
Sheets(arSheets).Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Thanks a lot, it saved a lot of time.