Copy Rows of info until next non empty row

mjacquot82

New Member
Joined
Jan 2, 2018
Messages
34
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#StartAmountEndtotal
Chicken Little244116:00:00 AM24:15:00 AM50,300
7:00:00 AM1
8:00:00 AM2
9:00:00 AM1
Yogi Bear2441211:00:00 PM24:15:00 AM54,000
11:00:00 PM2
Snow White245448:00:00 PM58:00:00 AM45 220
9:00:00 PM3
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
try this, let me know how it works.
VBA Code:
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
 
Upvote 0
It works !
try this, let me know how it works.
VBA Code:
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.
 
Upvote 0
try this

VBA Code:
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
 
Upvote 0
try this

VBA Code:
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]
 
Upvote 0
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 ?
 
Upvote 0
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 ?
 
Upvote 0

Forum statistics

Threads
1,223,248
Messages
6,171,027
Members
452,374
Latest member
keccles

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