Excel VBA automation - copy string "x" number of times by cell value
I am trying to automate Excel in a way that saves me from countless hours of tedious data entry. Here is my problem.
We need to print barcodes for our entire inventory, which includes 4000 options each with a specific quantity.
Shopify is our ecommerce platform and they do not support custom exports; however, it can export all variants of CSV, which includes an inventory column.
We use Dymo for our barcode printing hardware / software. Dymo will only print one label per line (it ignores the count column).
Is there a way to automate excel to duplicate row "x" the number of times based on the value in the inventory column?
Here's some sample data:
- If column N = 0, ignore and go to next line
- If column N> 1, copy the current row, the number "N" (on a separate sheet)
I tried to find someone who did something similar to change the code, but after an hour of searching, I'm still right where I started. Thanks in advance for your help!
source to share
David beat me, but the alternative approach didn't hurt anyone.
Consider the following data
Item Cost Code Quantity
Fiddlesticks 0.8 22251554787 0
Woozles 1.96 54645641 3
Jarbles 200 158484 4
Yerzegerztits 56.7 494681818 1
With this function
Public Sub CopyData()
' This routing will copy rows based on the quantity to a new sheet.
Dim rngSinglecell As Range
Dim rngQuantityCells As Range
Dim intCount As Integer
' Set this for the range where the Quantity column exists. This works only if there are no empty cells
Set rngQuantityCells = Range("D1", Range("D1").End(xlDown))
For Each rngSinglecell In rngQuantityCells
' Check if this cell actually contains a number
If IsNumeric(rngSinglecell.Value) Then
' Check if the number is greater than 0
If rngSinglecell.Value > 0 Then
' Copy this row as many times as .value
For intCount = 1 To rngSinglecell.Value
' Copy the row into the next emtpy row in sheet2
Range(rngSinglecell.Address).EntireRow.Copy Destination:= Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
' The above line finds the next empty row.
Next
End If
End If
Next
End Sub
Produces the following output on sheet2
Item Cost Code Quantity
Woozles 1.96 54645641 3
Woozles 1.96 54645641 3
Woozles 1.96 54645641 3
Jarbles 200 158484 4
Jarbles 200 158484 4
Jarbles 200 158484 4
Jarbles 200 158484 4
Yerzegerztits 56.7 494681818 1
The caveats with this code are that the Count column cannot have empty fields. I used D, so feel free to replace N for your case.
source to share
Should be enough to get you started:
Sub CopyRowsFromColumnN()
Dim rng As Range
Dim r As Range
Dim numberOfCopies As Integer
Dim n As Integer
'## Define a range to represent ALL the data
Set rng = Range("A1", Range("N1").End(xlDown))
'## Iterate each row in that data range
For Each r In rng.Rows
'## Get the number of copies specified in column 14 ("N")
numberOfCopies = r.Cells(1, 14).Value
'## If that number > 1 then make copies on a new sheet
If numberOfCopies > 1 Then
'## Add a new sheet
With Sheets.Add
'## copy the row and paste repeatedly in this loop
For n = 1 To numberOfCopies
r.Copy .Range("A" & n)
Next
End With
End If
Next
End Sub
source to share
It may be a little late to answer, however it may help others. I tested this solution in Excel 2010. Let's say "Sheet1" is the name of the sheet where your data is located and "Sheet2" is the sheet where you want to repeat the data. Assuming you have these sheets, try the code below.
Sub multiplyRowsByCellValue()
Dim rangeInventory As Range
Dim rangeSingleCell As Range
Dim numberOfRepeats As Integer
Dim n As Integer
Dim lastRow As Long
'Set rangeInventory to all of the Inventory Data
Set rangeInventory = Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("D2").End(xlDown))
'Iterate each row of the Inventory Data
For Each rangeSingleCell In rangeInventory.Rows
'number of times to be repeated copied from Sheet1 column 4 ("C")
numberOfRepeats = rangeSingleCell.Cells(1, 3).Value
'check if numberOfRepeats is greater than 0
If numberOfRepeats > 0 Then
With Sheets("Sheet2")
'copy each invetory item in Sheet1 and paste "numberOfRepeat" times in Sheet2
For n = 1 To numberOfRepeats
lastRow = Sheets("Sheet1").Range("A1048576").End(xlUp).Row
r.Copy
Sheets("Sheet1").Range("A" & lastRow + 1).PasteSpecial xlPasteValues
Next
End With
End If
Next
End Sub
This solution is a slightly modified version of David Zemens' solution.
source to share