VB -Copy and Paste Nested Loop in Excel

Question

So I have a problem that this is generating random results with the Qty.

I am trying to make each qty (in their qty's) a new line on a new spreadsheet.

It creates the new sheet, and references the old sheet... the code copies and pastes the lines... It just doesn't loop the do while in the correct amount of times. I have tried different operands (>= 0) and altering the variable values to make this work.

It does not seem to be patternized as to why it is happening. Sometimes it does it in the correct amount of loop cycles, others it does not. This occurs on multiple values. Any help is greatly appreciated.

Sub copyPasta()
'
' copyPasta Macro
' This will take the qty, if greater than one  in Column C and copy the row 
'to a new sheet the amount of time the qty.
'
'
'Set Variable Types
Dim lineItemQty As Integer
Dim newLineItemQty As Integer
Dim LastRow As Integer
Dim strSheetName As String
Dim newSheetName As String
Dim i As Integer

Application.DisplayAlerts = False

'name a variable after the existing active sheet
strSheetName = ActiveSheet.Name
'add a sheet in addition to the current
Sheets.Add After:=ActiveSheet
'set a variable used in loops to the sheet being copied to
newSheetName = ActiveSheet.Name
'Return to first sheet
Sheets(strSheetName).Activate
' Set For Loop to max row
LastRow = Sheets(strSheetName).Range("C:C").Find("*", searchdirection:=xlPrevious).Row

'for loop to run through all rows
For i = 3 To LastRow Step 1

    'initializing variable to Qty value in table
    lineItemQty = Range("C" & i).Value

    'initializing variable within in line of for looping
    newLineItemQty = lineItemQty

    'do while loop to keep copying/pasting while there are still qty's
        Do While newLineItemQty > 0

        'do while looped copy and paste
            'copy the active row
                Sheets(strSheetName).Activate
                Rows(i).Select
                Selection.Copy
            'paste active row into new sheet
                Sheets(newSheetName).Select
                Rows("3:3").Select
                Selection.Insert Shift:=xlDown


            newLineItemQty = newLineItemQty - 1

        Loop
Next i

Application.DisplayAlerts = True

End Sub

Answer 1

You can consider using (or taking parts from) the below alternative. A couple of note worthy notes are

1. You should avoid using .Select and .Activate. See here for details
2. Life is easier when you declare short variables. Here we just have ws for worksheet and ns for newsheet. You then need to actively state what sheet you are refferring to in your code (instead of using .Select or .Activate to do so by prefixing all objects with the appropriate worksheet variable)
3. You do not need to add Step 1 in your loop. This is the default - you only need to add this when you are deviating from the default!
4. There are a few ways to add sheets. Nothing wrong with the way you did - here is just an alternative (yay learning) that happens to be my preferred method.
5. To copy n many times, just create a nested loop and for 1 to n. Notice we never really use the variable n inside the loop which means the exact same operation will execute, we just want it to execute n times.

---

Sub OliveGarden()

Dim ws As Worksheet: Set ws = ThisWorkbook.ActiveSheet
Dim ns As Worksheet: Set ns = ThisWorkbook.Sheets.Add(After:=Sheets(ThisWorkbook.Sheets.Count))
    ns.Name = ws.Name & " New"

Dim i As Long, c As Long

'Application.ScreenUpdating = False
For i = 3 To ws.Range("C" & ws.Rows.Count).End(xlUp).Row
    If ws.Range("C" & i) > 0 Then
        For c = 1 To ws.Range("C" & i)
            LRow = ns.Range("A" & ns.Rows.Count).End(xlUp).Offset(1).Row
            ws.Range("C" & i).EntireRow.Copy
            ns.Range("A" & LRow).PasteSpecial xlPasteValues
        Next c
    End If
Next i
'Application.ScreenUpdating = True

End Sub