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]

Thry something along the lines of:

Sub ZEBRA_LABEL()
Dim r As Long, StrPrn As String
StrPrn = Application.ActivePrinter
Application.ActivePrinter = "ZEBRA"
With ActiveSheet
  With .PageSetup
    .FitToPagesWide = 1
    .FitToPagesTall = False
  End With
  For r = 5 To .Cells.SpecialCells(xlCellTypeLastCell).Row Step 6
    If Range("a" & r).Value > 0 Then
      With .PageSetup
        .PrintArea = "$B$" & r & ":$E$" & r + 3
        Application.Dialogs(xlDialogPrinterSetup).Show
        ActiveSheet.PrintOut Copies:=Range("A" & r).Value
      End With
    End If
  Next
End With
Application.ActivePrinter = StrPrn
End Sub

using the desired printer's name where you see 'ZEBRA'. To get the name of a given printer, you'd activate it temporarily, then use something like:

Sub GetPrinter()
MsgBox Application.ActivePrinter
End Sub

 

[Shadkng]

Hi, would you be kind enough to explain the code starting with r =5. I understand the printer stuff. Thanks

 

[Macropod]

The key to understanding the loop is the appreciate that the r values correspond with row numbers. Thus, the line:
For r = 5 To .Cells.SpecialCells(xlCellTypeLastCell).Row Step 6
starts a loop from row 5 to the last-used row, in increments of 6 rows, thus processing rows 5, 11 and so on just as it appears you intend.

Likewise, the test:
If Range("a" & r).Value > 0 Then
is equivalent to your:
If Range("a5").Value > 0 Then
If Range("a11").Value > 0 Then
and so on.

And, since you're defining the print area in 4-row ranges, the line:
.PrintArea = "$B$" & r & ":$E$" & r + 3
achieves that, too. Form the foregoing, you should be able to work out what:
ActiveSheet.PrintOut Copies:=Range("A" & r).Value
represents.

 

[Shadkng]

Hi, works pretty well. Thanks for the instructions. I'm having trouble with the active printer...followed your instructions to get the name of the printer, but I'm getting the following error: Run time error 1004 - method active printer of object-application failed.

I have another question...if I wanted to copy and paste the same group of lines many times below it would be the same type of code? Instead of setting a range we would select, etc? Would you be able to help with that later?

Thanks very much

 

[Macropod]

Hi, works pretty well. Thanks for the instructions. I'm having trouble with the active printer...followed your instructions to get the name of the printer, but I'm getting the following error: Run time error 1004 - method active printer of object-application failed.

Did you include the full string returned by the GetPrinter, plus the double quotes?

if I wanted to copy and paste the same group of lines many times below it would be the same type of code? Instead of setting a range we would select, etc?

The same code should work unmodified regardless of how many lines you might have.

 

[Shadkng]

I entered the printer name within the quotes that were in your line. Do I need another set of double quotes?

I am having a small issue where my labels are printing one at a time instead of continuously which I assume has nothing to do with the code but I figured I would ask anyway. I guess each loop is considered a separate print job?

 

[Macropod]

I entered the printer name within the quotes that were in your line. Do I need another set of double quotes?

Just keeping the existing double quotes should be sufficient. Make sure you have the same capitalisation, spaces, etc.

I am having a small issue where my labels are printing one at a time instead of continuously which I assume has nothing to do with the code but I figured I would ask anyway. I guess each loop is considered a separate print job?

Correct; otherwise, you need to approach the task differently (e.g. inserting page breaks into Excel, then printing as a single job).

 

[Shadkng]

Yes I'm sure you're right. The funny thing is the printer is printing several labels at a time and sometimes not. Anyway, it doesn't affect the usefulness of the code as it works great. Maybe the macro is sending the data so quickly that the printer can't process it all and hiccups every few labels.

I don't think I explained my other question earlier clearly. I'm thinking of a way to have a macro copy down those 5 rows of text (which will later be formulas pulling data from another sheet) based on whether a cell has a quantity. I already do it in my other sheets but I am copying only lines down so it's easy. So my question was if I could, or with help, have that range, let's say b5:c10, copied down to b12:c17 - same idea as the print range you made. I would possibly use indirects because hard coded formulas would not work in this case. The reason is that my spreadsheet sometimes has hundreds of lines and each line has the data for one label. The number of lines change per file so I don't want to hard code formulas for hundreds of lines when they will not be needed. So the macro will check how many lines are populated and then copy down as many groups of those 5 lines as needed.

 

[Macropod]

This is the first mention you've made of getting data from another sheet. If the goal is to pull data from another sheet for printing, it would be better to have just the one print range (i.e. $B$5:$E$8), then use a loop to go through the source sheet and populate whichever output cells need the variable data, printing on each iteration.