Excel VBA automation - copy row "x" number of times based on cell value

Question

I'm attempting to automate Excel in a way that will save me countless hours of tedious data entry. Here's my problem.

We need to print barcodes for all of our inventory, which includes 4,000 variants each with a specific quantity.

Shopify is our e-commerce platform and they do not support customized exports; however, can export a CSV of all variants, which includes an inventory count column.

We use Dymo for our barcode printing hardware/software. Dymo will only print one label per row (it ignores the quantity column).

Is there a way to automate excel to duplicate the row "x" number of times based on the value in the inventory column?

Here's a sample of the data:

https://www.evernote.com/shard/s187/sh/b0d5b92a-c5f6-469c-92fb-3d4e03d97544/d176d3448ba0cafbf3d61506402d9e8b/res/254447d2-486d-454f-8871-a0962f03253d/skitch.png

• If Column N = 0, ignore and move to next row
• If Column N > 1, copy current row, "N" number of times (to a separate sheet)

I tried to find someone who had done something similar so that I could modify the code, but after an hour of searching I'm still right where I started. Thank you in advance for the help!

Answer 1

David beat me to it but an alternate approach never 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 is that there can be no empty fields in the Quantity column. I used D so feel free to substitute N for your case.