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

T

tomwhi

New Member
Joined
May 13, 2013
Messages
35
Hi,

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

[TABLE="width: 336"]
<COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY>[TR]
[TD="class: xl66, width: 192, bgcolor: #ebf1de, colspan: 3"]From ('Sheet1')[/TD]
[TD="class: xl67, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 192, bgcolor: #ebf1de, colspan: 3"]To ('Sheet2')[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow"]Position[/TD]
[TD="class: xl68, bgcolor: yellow"]HC[/TD]
[TD="class: xl68, bgcolor: yellow"]PA[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: yellow"]Position[/TD]
[TD="class: xl68, bgcolor: yellow"]HC[/TD]
[TD="class: xl68, bgcolor: yellow"]PA[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Test1[/TD]
[TD="class: xl65, bgcolor: transparent"]5[/TD]
[TD="class: xl65, bgcolor: transparent"]RIG1[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Test1[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]RIG1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Test2[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]RIG2[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Test1[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]RIG1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Test3[/TD]
[TD="class: xl65, bgcolor: transparent"]8[/TD]
[TD="class: xl65, bgcolor: transparent"]RIG3[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Test1[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]RIG1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Test1[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]RIG1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Test1[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]RIG1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Test2[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]RIG2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Test3[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]RIG3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Test3[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]RIG3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Test3[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]RIG3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Test3[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]RIG3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Test3[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]RIG3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Test3[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]RIG3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Test3[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]RIG3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Test3[/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]RIG3[/TD]
[/TR]
</TBODY>[/TABLE]
 
Sort by date Sort by votes
One way (not very elegant):
Code: 
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
tomwhi said:
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?
Click to expand...
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).
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1
Replies
7
Views
737
karl2022
K
C
  • Question Question
Create a new row for every date in row and create multiple copies of data to new worksheet based on cell number value for each date in the row VBA
Replies
0
Views
203
Chris258
C
B
  • Solved
VBA to save one file as multiple files in location
Replies
7
Views
1K
bh24524
B
N
  • Question Question
VBA Split and Fill only doing one line at a time. Help.
Replies
2
Views
305
NeoSez
N
I
  • Question Question
Move multiple rows from one table to another using conditional trigger
Replies
2
Views
967
ItsNursinTime
I

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top