First time posting. How do I turn one line/row into multiples based on a number

[tomwhi]

Hi,

how would I perform the below using either formula or VBA?
Thanks

From ('Sheet1')		To ('Sheet2')
Position	HC	PA		Position	HC	PA
Test1	5	RIG1		Test1	1	RIG1
Test2	1	RIG2		Test1	1	RIG1
Test3	8	RIG3		Test1	1	RIG1
				Test1	1	RIG1
				Test1	1	RIG1
				Test2	1	RIG2
				Test3	1	RIG3
				Test3	1	RIG3
				Test3	1	RIG3
				Test3	1	RIG3
				Test3	1	RIG3
				Test3	1	RIG3
				Test3	1	RIG3
				Test3	1	RIG3

<COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY>
</TBODY>

 

[p45cal]

One way (not very elegant):

Sub blah()
Sheets("Sheet2").Range("A1").Resize(, 3) = Array("Position", "HC", "PA")
destrw = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
Set xxx = Sheets("Sheet1").Range("A1").CurrentRegion.Resize(, 3)
Set xxx = xxx.Offset(1).Resize(xxx.Rows.Count - 1)
For Each rw In xxx.Rows
    For i = 1 To rw.Cells(2).Value
        rw.Copy Sheets("Sheet2").Cells(destrw, "A")
        Sheets("Sheet2").Cells(destrw, "B") = 1
        destrw = destrw + 1
    Next i
Next rw
End Sub

 

[tomwhi]

Thank you for your reply,

I should have said this in my previous post, but I am a complete beginner with VBA. Could you highlight (or something) the parts of the code above that I will need to alter/change?

 

[p45cal]

All refs to Sheet2 should be altered to match the name of the destination sheet.
All refs to Sheet1 should be altered to match the name of the source sheet.

Range("A1").CurrentRegion
is the same as doing F5, Special… Current region starting with the single cell A1 selected.
I then taken the leftmost 3 columns of the resultant range.
Test it out for yourself to see whether you get the right results, if cell A1 isn't part of that range, select another cell (say, the cell containing Position on the source sheet) and F5, Special Cells… Current region. If the first three columns of the resultant selected range is the range you want to process, then change A1 in the code to the addres of that cell.

In:
destrw = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
the "A" refers to the leftmost column of the results on the destination sheet. Change the "A" to something else if you want the results starting in another column.

Following on from that, in:
rw.Copy Sheets("Sheet2").Cells(destrw, "A")
change the "A" there to match.

Finally, in the line directly below:
Sheets("Sheet2").Cells(destrw, "B") = 1
the "B" is the column immediately to the right (the middle of the three columns being copied over), so adjust that "B" to the column letter to the immediate right of whatever you changed "A" to.

 

[Rick Rothstein]

I should have said this in my previous post, but I am a complete beginner with VBA. Could you highlight (or something) the parts of the code above that I will need to alter/change?

Why would you need to alter/change any of the code that p45cal posted? Did you, perhaps, simplify your setup when you posted it? My advice for the next time you ask a question... do not simplify the problem for us... all that gets you (as you have seen in this thread) is a great answer to a question you really do not have and about which you really do not care. I have seen it so many times... the end result of simplifying your question is you get a solution you cannot figure out how to change which ends up giving more work to the volunteers here who responded to your question (as they either have to explain or modify their solution for you).

 

[tomwhi]

Thanks Again for the posts.

As for why I wanted to know if there is anything I needed to change. Two main reasons.
1) As i mentioned earlier I am a VBA novice, so I simply wanted to understand for about the code (what is varible etc)
2) i thought the 'xxx's' and the 'blah' maybe refering to something I would need to input.

Thank you again for your help. I will try it out now

 

[tomwhi]

Thanks @p45cal this is working well.