Does anybody know how I can repeat rows in a spreadsheet (or into another spreadsheet) by n number of times specified in the cell in that row to another cell
ie from this table:
Column A Column B
Peter 3
James 7
David 4
I need to produce this table:
Column C Column D
Peter 1
Peter 2
Peter 3
James 1
James 2
James 3
James 4
James 5
James 6
James 7
David 1
David 2
David 3
David 4
header 1 header 2 header 3
Peter male 3
james male 7
David male 4
try this macro and see sheet 2
Sub test() Dim rA As Range, cA As Range, dest As Range, j As Long, k As Long Dim nname As String, sex As String 'data is in sheet1 With Worksheets("sheet1") Set rA = Range(.Range("A2"), .Range("A2").End(xlDown)) For Each cA In rA j = .Cells(cA.Row, "C") nname = cA sex = .Cells(cA.Row, "B") With Worksheets("sheet2")
For k = 1 To j Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) dest = nname dest.Offset(0, 1) = sex dest.Offset(0, 2) = j & "." & k Next k End With Next cA End With
thanks so much~~~ it works for me! saved me a lot of time!
but another query is, if I have multiple columns to be repeated based on the number in col x, do I change to something like this:
Sub test()
Dim rA As Range, cA As Range, dest As Range, j As Long, k As Long
Dim batch As String, location As String, code As String, color As String
'data is in sheet1
With Worksheets("sheet10")
Set rA = Range(.Range("A2"), .Range("A2").End(xlDown))
For Each cA In rA
j = .Cells(cA.Row, "E")
batch = cA
location = .Cells(cA.Row, "B")
code = .Cells(cA.Row, "C")
color = .Cells(cA.Row, "D")
With Worksheets("sheet11")
For k = 1 To j
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
dest = batch
dest.Offset(0, 1) = location
dest.Offset(0, 2) = code
dest.Offset(0, 3) = color
dest.Offset(0, 4) = j & "." & k
Next k
End With
Next cA
End With
suppose row 1 is header row
row 2, 3 and 4 has data
hdng1 hdng2
Peter 3
James 7
David 4
try this macro
Sub test() Dim r As Range, c As Range, j As Integer, dest As Range, k As Integer Set r = Range(Range("A2"), Range("A2").End(xlDown)) For Each c In r j = c.Offset(0, 1) Set dest = Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) For k = 1 To j dest.Offset(k - 1, 0) = c dest.Offset(k - 1, 1) = k Next k Next c
data
col 1 col 2 col 3
header 1 header 2 header 3
Peter male 3
James male 7
David male 4
output
col 1 col 2 col 3 col 4
Peter male 3 1
Peter male 3 2
Peter male 3 3
James male 7 1
James male 7 2
James male 7 3
James male 7 4
James male 7 5
James male 7 6
James male 7 7
David male 4 1
David male 4 2
David male 4 3
David male 4 4
but another query is, if I have multiple columns to be repeated based on the number in col x, do I change to something like this:
Sub test()
Dim rA As Range, cA As Range, dest As Range, j As Long, k As Long
Dim batch As String, location As String, code As String, color As String
'data is in sheet1
With Worksheets("sheet10")
Set rA = Range(.Range("A2"), .Range("A2").End(xlDown))
For Each cA In rA
j = .Cells(cA.Row, "E")
batch = cA
location = .Cells(cA.Row, "B")
code = .Cells(cA.Row, "C")
color = .Cells(cA.Row, "D")
With Worksheets("sheet11")
For k = 1 To j
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
dest = batch
dest.Offset(0, 1) = location
dest.Offset(0, 2) = code
dest.Offset(0, 3) = color
dest.Offset(0, 4) = j & "." & k
Next k
End With
Next cA
End With
End Sub