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

 

[Shadkng]

That's an idea, but I think I'm more comfortable having them all displayed - sometimes we may need to edit some of them. Maybe I can have both options. I cannot send you the entire workbook but I can build a small version that would show better what I'm doing. I appreciate your help...I'm learning the basics of VBA and I usually try to build a macros on my own by finding similar ones online. But this is way beyond me.

 

[Macropod]

That's an idea, but I think I'm more comfortable having them all displayed - sometimes we may need to edit some of them. Maybe I can have both options. I cannot send you the entire workbook but I can build a small version that would show better what I'm doing. I appreciate your help...I'm learning the basics of VBA and I usually try to build a macros on my own by finding similar ones online. But this is way beyond me.

In that case, one would need to replicate the fixed content in $B$5:$E$8 and update the other cell references to point to the variable data. Ultimately, that's a lot more work and now involves to quite separate processes: one to create the data in the output format; the other (which you already have) to loop through the output data and print it.

 

[Shadkng]

I thought about it and I think the following will solve my problem. Below is a link to a file which simulates what I need to do in my main spreadsheet. It shows 2 sample labels separated by one line, so the range of "D5:E11" needs to be copied down to the position of the 2nd label and so on. The number of copies will be according to the number in cell "A1" which will be variable (this number represents the number of lines with data on the main sheet). The cells in the target range will have formulas to pull the data which I already have working fine in my main sheet. So my only hurdle is the automatic copying so we don't have do it manually. Thank you

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

 

[Macropod]

I don't see what benefit there is in copying the same data over and over for printing when you can tell the printer how many copies to print without any of that. Seems like a waste of effort to me - especially if, as you said previously, you said the data might need editing.

 

[Shadkng]

It's not the same data, actually each label will have different data that it pulls from another sheet. So label 1 will pull data from several fields from line 1, and label 2 will pull the data from line 2 and so on for hundreds of labels. I am using it now with the formulas in place over a hundred times. But it's a waste if I only need a few labels to have all those formulas not doing anything. I'm sorry if I haven't explained it so clearly.

 

[Macropod]

It would be helpful if you would actually post a snapshot of both worksheets here. You can do that using one of the posting aids mentioned in the forum guidelines: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[Shadkng]

Hi Paul, I was able to reduce the spreadsheet to a small sample to show you exactly what I am doing. Below is the link. You will see a few labels that are pulling the information from sheet "finalorder". Some of the label fields are using index/match to match the line numbers in column A on sheet "labels" to match the line number in finalorder. Then cell A1 in finalorder shows the number of lines that have data and that's the number I want to use as to how many label ranges need to be copied down. Sometimes we will change some of the data in various labels using the misc fields, so I prefer that they all be displayed. Also, I will be using this same format to create other labels which will have to be displayed as well. The range that will need to be copied is A10:D16 by the count in A1. Hope this helps thanks. By the way, I already started using the code you wrote for printing the labels and it is a game changer for my business! Before we were using Zebra software to import the data to print which was time consuming. Thanks

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

 

[Shadkng]

I forgot to mention that row 24 on sheet finalorder always has data in it so therfore the first label on the label sheet will always be populated and can be used as the base label if necessary.

 

[Shadkng]

Hi, I just happen to find this code on a search and it actually does exactly what I need. Just wanted to let you know before you do any work on it. Thanks

Sub CopyMulti()
Range("A1:D14").Copy Range("A15").Resize(14 * Range("E1").Value)
End Sub

 

[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