I need a VB script that will spawn duplicate records
I need to be able to generate duplicate records based on a value in each record and assign a count value to each new record. The new records would appear in a different/existing sheet.
This is a very simplistic example; there would actually be much more data to duplicate:
In sheet1:
Product_Code Number_Of_Cases
123456-1 10
456789-0 5
would turn into:
In sheet2:
Product_Code Case_Number
123456-1 1
123456-1 2
123456-1 3
123456-1 4
123456-1 5
123456-1 6
123456-1 7
123456-1 8
123456-1 9
123456-1 10
456789-0 1
456789-0 2
456789-0 3
456789-0 4
456789-0 5
This was solved on
This was solved on excelguru.com by Bob Phillips:
Public Sub Duplicate()
Dim lastrow As Long
Dim numrows As Long
Dim i As Long
Application.ScreenUpdating = True
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1").Resize(lastrow, 2).Copy Worksheets("Sheet2").Range("A1")
End With
With Worksheets("Sheet2")
For i = lastrow To 2 Step -1
If .Cells(i, 2).Value > 1 Then
numrows = .Cells(i, "B").Value
.Rows(i + 1).Resize(numrows - 1).Insert
.Cells(i + 1, "A").Value = .Cells(i, "A").Value
.Cells(i, "B").Value = 1
.Cells(i + 1, "B").Value = 2
.Cells(i, "A").Resize(, 2).AutoFill .Cells(i, "A").Resize(numrows, 2)
End If
Next i
.Range("B1").Value = "Case_No"
End With
Application.ScreenUpdating = False
End Sub