Copy Rows of info until next non empty row

[mjacquot82]

Good day everyone,

Trying to have a button ( or multiple if needed ) that when clicked will be able to copy A2:F6 ( for example ) and paste in different range. But i also need it to be able to then select the info associated to the next name in column A and copy paste that one. The tricky part is that the # of empty rows between the names in column A will always be different. Where the information will be paste will always be the same, right now on my sheet its G21.

Not sure if I am making what i am trying to achieve clear here. Please any help would be appreciated.
A B C D E F

Name	#	Start	Amount	End	total
Chicken Little	24411	6:00:00 AM	2	4:15:00 AM	50,300
		7:00:00 AM	1
		8:00:00 AM	2
		9:00:00 AM	1
Yogi Bear	24412	11:00:00 PM	2	4:15:00 AM	54,000
		11:00:00 PM	2
Snow White	24544	8:00:00 PM	5	8:00:00 AM	45 220
		9:00:00 PM	3

 

[rpaulson]

try this, let me know how it works.

Sub Do_it()
lr = Cells(Rows.Count, "C").End(xlUp).Row

r1 = 2
x = ""

10

r2 = Cells(r1, "A").End(xlDown).Row - 1 ' Last Row
If r2 > lr Then
    r2 = lr
    x = "Yes"
End If

Range("A" & r1 & ":F" & r2).Copy Range("G21")
r1 = r2 + 1

If x = "Yes" Then Exit Sub
GoTo 10 'do the next one


End Sub

-Ross

 

[mjacquot82]

It works !

try this, let me know how it works.

Sub Do_it()
lr = Cells(Rows.Count, "C").End(xlUp).Row

r1 = 2
x = ""

10

r2 = Cells(r1, "A").End(xlDown).Row - 1 ' Last Row
If r2 > lr Then
    r2 = lr
    x = "Yes"
End If

Range("A" & r1 & ":F" & r2).Copy Range("G21")
r1 = r2 + 1

If x = "Yes" Then Exit Sub
GoTo 10 'do the next one


End Sub

-Ross

I assigned it to a command button and it works if i run through the steps using F8. If i just hit the command button it just copy pastes all of them so fast that it ends up just being the last name's information i see. Also if the previous name had more info then some of that is left in the pasted area. Is there a way to make it that it works one at a time per button click and clears the pasted area before pasting the new info ? Thank you very much for this by the way.

 

[rpaulson]

try this

Sub Do_it()
lr = [I21].End(xlDown).Row
Range("G21:K" & lr).ClearContents

lr = Cells(Rows.Count, "C").End(xlUp).Row
r1 = 2
x = ""

10
r2 = Cells(r1, "A").End(xlDown).Row - 1 ' Last Row
If r2 > lr Then
    r2 = lr
    x = "Yes"
End If

Range("A" & r1 & ":F" & r2).Copy Range("G21")
r1 = r2 + 1

If x = "Yes" Then Exit Sub
Answer = MsgBox("Run it again", vbQuestion + vbYesNo + vbDefaultButton2, "Again")
If Answer = vbNo Then Exit Sub

GoTo 10 'do the next one

End Sub

 

[mjacquot82]

try this

Sub Do_it()
lr = [I21].End(xlDown).Row
Range("G21:K" & lr).ClearContents

lr = Cells(Rows.Count, "C").End(xlUp).Row
r1 = 2
x = ""

10
r2 = Cells(r1, "A").End(xlDown).Row - 1 ' Last Row
If r2 > lr Then
    r2 = lr
    x = "Yes"
End If

Range("A" & r1 & ":F" & r2).Copy Range("G21")
r1 = r2 + 1

If x = "Yes" Then Exit Sub
Answer = MsgBox("Run it again", vbQuestion + vbYesNo + vbDefaultButton2, "Again")
If Answer = vbNo Then Exit Sub

GoTo 10 'do the next one

End Sub

[/QUOTE]

 

[mjacquot82]

I guess the best way to describe what i am trying to do is that after a paste i have other function that i need to do with that information before moving on to the next name. When the message box pops up it doesn't allow me to do anything. Is there a way to change it so that after is pastes one it stops until i press the command button again ?

 

[mjacquot82]

I guess the best way to describe what i am trying to do is that after a paste i have other function that i need to do with that information before moving on to the next name. When the message box pops up it doesn't allow me to do anything. Is there a way to change it so that after is pastes one it stops until i press the command button again ?

In a way i guess what i am wondering is, in your code if i were to press "no" after running it for 2 or 3 times, can i then press the command button again and have the code start back from where i stopped it instead of from the beginning of the list ?