Does anybody know how I can repeat rows in a spreadsheet by n number of times specified in cell B in that row
ie from this table:
Column A, Column B, Columns C-Z have other data that needs to be duplicated to each row too
Jack, 2, Data, Data, Data, etc...
Mary, 4, Data, Data, Data, etc...
Henry, 20, Data, Data, Data, etc...
Ross, 3, Data, Data, Data, etc...
I tried running this:
Sub CopyData()
Dim xRow As Long
Dim VInSertNum As Variant
xRow = 1
Application.ScreenUpdating = False
Do While (Cells(xRow, "A") <> "")
VInSertNum = Cells(xRow, "B")
If ((VInSertNum > 1) And IsNumeric(VInSertNum)) Then
Range(Cells(xRow, "A"), Cells(xRow, "Z")).Copy
Range(Cells(xRow + 1, "A"), Cells(xRow + VInSertNum - 1, "Z")).Select
Selection.Insert Shift:=xlDown
xRow = xRow + VInSertNum - 1
End If
xRow = xRow + 1
Loop
Application.ScreenUpdating = False
End Sub
But I'm getting a bug in the "If ((VInSertNum > 1) And IsNumeric(VInSertNum)) Then" line. Any idea?
- Posts
- 3
- Registration date
- Tuesday July 9, 2019
- Status
- Member
- Last seen
- July 9, 2019
-- Posts
- 3
- Registration date
- Tuesday July 9, 2019
- Status
- Member
- Last seen
- July 9, 2019
-Sub CopyData()
Dim xRow As Long
Dim VInSertNum As Variant
xRow = 1
Application.ScreenUpdating = False
Do While (Cells(xRow, "A") <> "")
VInSertNum = Cells(xRow, "B")
If ((VInSertNum > 1) And IsNumeric(VInSertNum)) Then
Range(Cells(xRow, "A"), Cells(xRow, "Z")).Copy
Range(Cells(xRow + 1, "A"), Cells(xRow + VInSertNum - 1, "Z")).Select
Selection.Insert Shift:=xlDown
xRow = xRow + VInSertNum - 1
End If
xRow = xRow + 1
Loop
Application.ScreenUpdating = False
End Sub
But I'm getting a bug in the "If ((VInSertNum > 1) And IsNumeric(VInSertNum)) Then" line. Any idea?
- Posts
- 12506
- Registration date
- Monday June 3, 2013
- Status
- Moderator
- Last seen
- July 10, 2019
1131 -msgbox("the variable is " & VInSertNum)
BTW, is there a numeric value in cell(xRow,"B")?
You may have to put Thisworkbook.worksheets("yoursheetname").Cells(xRow,"B") in the variable initialization of:
VInSertNum = Thisworkbook.WorkSheets.("yoursheetname").Cells(xRow, "B").value
Give it try!