Re: Copy Rows Based On A Cell Value
couple of points - when I suggested that you post the question along with the sample data in the forum, I meant for you to pose your question and not just attach the spreadsheet - this was so that you could broaden the number of people who could potentially look at your problem and come up with a solution...
Also - I don't believe that this is
wow! This must be the most user-unfriendly site I have ever seen)
In fact I believe the community that use this forum to be one of the most friendly and helpful I have ever come across and the site is great - I have learnt most of what I know from this forum thanks to all you oz-griders !!
So, back to your problem - I reckon your requirements can be met with the following code...hopefully the comments fully explain what is going on...if you have any questions, please respond to this post and I'll endeavour to help
Dim pasteTo As Range, pasteTo2 As Long, cl As Range
'Clears any existing data on the destination sheet (sheet2 in the example given)
'Copy the header row from sheet1 to sheet2
'This selects the current used region on sheet1
'assuming that you have a properly laid out table
'ie no empty rows etc
'Now loop down a resized range which is the
'first column in the selected range
For Each cl In .Offset(1).Resize(.Rows.Count - 1, 1)
'Set the cell where the next set of date should
'be pasted to
Set pasteTo = Sheet2.Cells(65536, 1).End(xlUp).Offset(1)
'Copy the single row across
cl.Resize(, 5).Copy Destination:=pasteTo
'If the Count is is greater than one,then
'fill down the approrpite number of row
'by using a resized range
If cl.Offset(, 5) <> 1 Then
pasteTo.Resize(cl.Offset(, 5).Value, 5).FillDown
'populate the count column with '1'
With .Offset(1, 5).Resize(.Rows.Count - 1, 1)
.Value = 1
Hopefully my comments help (although I must profess I am not the best at explaining what I do sometimes !!!)
Anyway - was lovely to post here again - haven't visited for a while as my role has moved me much more towards SQL and the like...
All this in preparation for the Uk Bank holiday weekend - enjoy...