VBA to print labels from excel

[Shadkng]

Hi, I am trying to print labels from an excel file to a Zebra thermal printer. I have the code working OK so far but I think it needs improvement. The idea is to print several ranges of text sequentially - the range of each section will not change. I included my test excel file with just 2 ranges and with a cell next to each range containing the number of copies. I may have as many as 20 or 30 ranges to print one after the other in one pass. So I was going to repeat the code that many times but I doubt that's the best way. Below is a link to the file on dropbox. Also see code below.

I was also having trouble naming the printer in the code. I was using the name of the printer in quotes as I saw online, but do I have to name the port? Thanks for the help.

https://www.dropbox.com/s/214yglxcqzid785/test label.xlsm?dl=0

Sub ZEBRA_LABEL3()
If Range("a5").Value > 0 Then
With ActiveSheet.PageSetup
.PrintArea = "$B$5:$E$8"
.FitToPagesWide = 1
.FitToPagesTall = False
Application.Dialogs(xlDialogPrinterSetup).Show
ActiveSheet.PrintOut Copies:=Range("A5").Value
End With
End If
If Range("a11").Value > 0 Then
With ActiveSheet.PageSetup
.PrintArea = "$B$11:$E$14"
.FitToPagesWide = 1
.FitToPagesTall = False
ActiveSheet.PrintOut Copies:=Range("A11").Value
End With
End If
End Sub

 

[Macropod]

To print a text label, simply use a trimmed version of the macro you first posted:

Sub Test_Print()
If Range("A5").Value > 0 Then
  With ActiveSheet.PageSetup
    .PrintArea = "$B$5:$E$8"
    .FitToPagesWide = 1
    .FitToPagesTall = False
    Application.Dialogs(xlDialogPrinterSetup).Show
    ActiveSheet.PrintOut Copies:=1
  End With
End If
End Sub

PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.

 

[Shadkng]

Hi, I should have known that! Sorry to bother you again.

 

[Shadkng]

Hi Paul, the code below you wrote for printing labels which works perfectly. I am wondering if you could modify it so I can print a test label before printing the complete job. So I want to just print the fixed range of C10:D16. I would like the number of copies to be based on the value in cell B1. I need the printers dialogue box to remain. Thank you in advance.

Sub ZEBRA_LABEL_FINAL()
Dim r As Long, StrPrn As String
StrPrn = Application.ActivePrinter
With ActiveSheet
  With .PageSetup
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .LeftMargin = 0
    .TopMargin = 5
    .RightMargin = 0
    .BottomMargin = 5
  End With
  If Not (Application.Dialogs(xlDialogPrinterSetup).Show) Then Exit Sub
  
  For r = 10 To .Cells.SpecialCells(xlCellTypeLastCell).Row Step 8

    If Range("B" & r).Value > 0 Then
      With .PageSetup
        .PrintArea = "$C$" & r & ":$D$" & r + 6
         ActiveSheet.PrintOut Copies:=Range("B" & r).Value
      End With
    End If
  Next
End With
End Sub

Hi Macropod, I have been using the above code and it works so great! I am hoping you can add the following to the existing code for a new type of label. I hope I can explain it clearly.

I have to print labels that will need to have printed on them "1 of 10", 2 of 10, 3 of 10 etc. The quantity of 10 will change depending on the label run. The total number of labels needed will be in a specific cell - let's say sheet "PO" J3 would say 10. I don't need the "of 10" part to be printed - just the first number. The first number 1 would need to move to cell D12, then the 2 would need to step to D22, then 3 to D32, until 10 is reached. I realize the above code steps 8 but I have been changing the steps as my needs change, so can manage those changes. I hope this is just minimal work as I need it to solve an immediate need. I know there is a much better way to print this label which you explained in earlier posts about not needing to display the label. So I will comeback to you for that in due time. Thanks.

 

[Macropod]

What you're asking would require a re-write of the code. Furthermore, the 'Step 8' has nothing to do with the number of labels to print - that is calculated dynamically, via .Cells.SpecialCells(xlCellTypeLastCell).Row. The number of copies of each label to print is managed via Copies:=Range("B" & r).Value and, as that simply sends a command to the printer, rather than changing anything on the worksheet, there's nothing to output on the labels.

 

[Shadkng]

Yes I understand now that you explained it...what I am asking doesn't make sense. So if I come back to you would the following be possible? I would need to pull data from a number of cells on a specific sheet and populate a label template with let's say 10 fields. Then it would print a number of labels specified in a cell (also printing the "1 of "). I assume it would loop and do the same thing again and again. We would need only one label template which would be used over and over rapidly? This is the basic idea. Thanks

 

[Macropod]

Perhaps:

Sub ZEBRA_LABEL_FINAL()
Dim r As Long, c As Long, p As Long, StrPrn As String
StrPrn = Application.ActivePrinter
With ActiveSheet
  With .PageSetup
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .LeftMargin = 0
    .TopMargin = 5
    .RightMargin = 0
    .BottomMargin = 5
  End With
  If Not (Application.Dialogs(xlDialogPrinterSetup).Show) Then Exit Sub
  For r = 10 To .Cells.SpecialCells(xlCellTypeLastCell).Row Step 8
    .PageSetup.PrintArea = "$C$" & r & ":$D$" & r + 6
    p = Range("B" & r).Value
    For c = 1 To p
      Range("D" & r).Value = c & " of " & p
      ActiveSheet.PrintOut
    Next
    Range("D" & r).Value = ""
  Next
End With
End Sub

Nominally, the above code outputs your page count in the first line of each label at column D (I no idea what else you might have there). Adjust as necessary.

 

[Shadkng]

OK I will see if I can get it work. If not, I can send you the file and you'll see it exactly. Will be in touch tomorrow. Thanks

 

[Shadkng]

Hi Paul, I couldn't get the last code to work - I tried changing the code according to my new label. Below is the link to the file that I'm using so you could exactly what I'm doing. I know it's a bit crude but it prints OK, but no way to print "1 of".

On sheet "PO" there are 17 items (yellow) and one label template per item on the sheet "Labels". The total number of labels per job is in sheet labels B1. The total of each label is to the left of each label. As you can see in the code if the label count is zero then it won't print. You can use cell E12 as the count field and I can move things around afterwards. If it entails a lot of work it's fine if you can't do it. Thanks

https://www.dropbox.com/s/vubyik9tine78tw/HD label Zebra.xlsm?dl=0

 

[Macropod]

Following that link results in:

404
That file isn’t here any more

 

[Shadkng]

Sorry try this one.

https://www.dropbox.com/s/wxer4uc11eusfk4/HD label Zebra.xlsm?dl=0