Barcode Label Software Print Multiple of Same Label Solution
We are using the Barcode Label Software with data from Excel as an external data source.
We understand that the Barcode Label Software makes one label for each row in the data source, as is expected.
We ran across the need to have certain labels duplicated, based on a field in Excel, so I came up with this macro you can run in Excel (before importing the data into the Barcode Label Software).
Use:
1. Copy the macro to a new module and save the worksheet as a ‘macro-enabled workbook’ (file name ends with .xlsm), so that the macro is saved to the workbook itself. How to do this is easily found by searching Google.
2. Place a set of unique characters (“***” is the default) below the last row of data in Column A.
3. It is suggested that the ‘duplicate indicator’ (how many times you want to duplicate/print that specific label) be the rightmost column. Set this as srcCellOffset in the macro. In the example below, it is the Times to Print column. Any blanks in this column will automatically assume that the row should be only duplicated/printed once.
4. Rename the sheet titles in the macro, if necessary. By default, the source is named Sheet1, and the destination is named Sheet2.
Example of Sheet1:
Example of Sheet2:
After running the macro in Excel, the data is copied from Sheet1 to Sheet 2, as indicated in Sheet1's Times to Print column. The entire row is copied over, but in the Barcode Label Software, you can choose which fields to use.
Sheet 2 is what you would use as the data source for the Barcode Label Software.
Macro Code:
Sub DuplicateRows()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set thisWB = ThisWorkbook
'SET THESE ATTRIBUTES
'First Worksheet name
Set ws1 = thisWB.Sheets("Sheet1")
'Second Worksheet name
Set ws2 = thisWB.Sheets("Sheet2")
'Set how many offset cells the duplicate indicator is (not including leftmost cell)
srcCellOffset = 3
destCellOffset = 0
'Special character set to place under A column to indicate
'when to stop (end of data downward)
EndRowString = "***"
'DO NOT MODIFY ANYTHING BENEATH THIS LINE
'cell Ranges
Set src = ws1.Range("A:A")
Set dest = ws2.Range("A:A")
EndRow = ws1.Range("A:A").Find(EndRowString, searchdirection:=xlPrevious).Row
j = 2 'tracks down position for loop
ws2.Rows(1).Value = ws1.Rows(1).Value 'copy column headers
For i = 2 To (EndRow - 1) 'assumes column headers coppied
If (src(i).Offset(0, srcCellOffset) = "") Then
printCopies = 1 ' default to 1 if quantity not given
Else
printCopies = src(i).Offset(0, srcCellOffset)
End If
k = 0 'reset copy loop counter
Do While (k < printCopies)
ws2.Rows(j + k).Value = ws1.Rows(i).Value 'copy rows
k = k + 1 'increment count
Loop
j = j + k 'store current position to j for next copy
Next i
End Sub
Operating System:
Windows
Application:
Excel, IDAutomation Barcode Label Software
07-15-15  10.1 year(s) ago
 Report Abuse
|
Email this question link to friends 
My Profile 
