copy & paste dynamic range under latest info

[kimsrez]

Hi there, I am trying to copy e3:u3, and paste underneath the row that has the last value. So for the latest entry, it would be e25 onwards. Next month would be e26, etc.

So far, I could only make it work manually where the destination has to be updated every entry.



Can this be done? I'd really appreciate some help - thank you!!

 

[vcoolio]

Hello Kimsrez,
You may only need this:-

Sub Test()

Sheet1.[E3:U3].Copy Sheet1.Range("E" & Rows.Count).End(3)(2)

End Sub

As I don't know your sheet name, I've just used a sheet code (Sheet1) in the test code above.
I hope that this helps.

Cheerio,
vcoolio.

 

[kimsrez]

Hello Kimsrez,
You may only need this:-

Sub Test()

Sheet1.[E3:U3].Copy Sheet1.Range("E" & Rows.Count).End(3)(2)

End Sub

As I don't know your sheet name, I've just used a sheet code (Sheet1) in the test code above.
I hope that this helps.

Cheerio,
vcoolio.

Hi there,

I am sure your answer is absolutely straight forward but I am embarrassed to say that I am completely new to this and a wee bit lost.

If the sheet is called Sheet1 and the tab is called abc, would you please see if you can amend the script above?

Thanks so much


Edit: I figured it out!! You're a legend. Thanks so so much vcoolio!!

 

[kimsrez]

Hello Kimsrez,
You may only need this:-

Sub Test()

Sheet1.[E3:U3].Copy Sheet1.Range("E" & Rows.Count).End(3)(2)

End Sub

As I don't know your sheet name, I've just used a sheet code (Sheet1) in the test code above.
I hope that this helps.

Cheerio,
vcoolio.

Just out of interest vcoolio - what does the End(3)(2) do please??

 

[vcoolio]

Hello Kimsrez,

You're welcome. I'm glad to have been able to assist.
Good to know that you worked it out by yourself while I was fast asleep! Well done!

As for the End(3)(2):

In this line of code:
Sheet1.Range("E" & Rows.Count).End(3)(2)

is exactly the same as

Sheet1.Range("E"& rows.Count).End(xlUp).Offset(1) (or .Offset(1,0) if you prefer)

The 3 is the enumeration for xlUp and the 2 is the enumeration for Offset(1). Hence, the line of code, based on the target column "E", counts all the rows from the bottom of your worksheet(rows.count) up to the last row of data in your data set (End(xlUp)) then offsets the count down one row (Offset(1)) so that your previous data is not over written.

I hope that this helps.

Cheerio,
vcoolio.

 

[kimsrez]

Hello Kimsrez,

You're welcome. I'm glad to have been able to assist.
Good to know that you worked it out by yourself while I was fast asleep! Well done!

As for the End(3)(2):

In this line of code:
Sheet1.Range("E" & Rows.Count).End(3)(2)

is exactly the same as

Sheet1.Range("E"& rows.Count).End(xlUp).Offset(1) (or .Offset(1,0) if you prefer)

The 3 is the enumeration for xlUp and the 2 is the enumeration for Offset(1). Hence, the line of code, based on the target column "E", counts all the rows from the bottom of your worksheet(rows.count) up to the last row of data in your data set (End(xlUp)) then offsets the count down one row (Offset(1)) so that your previous data is not over written.

I hope that this helps.

Cheerio,
vcoolio.

Thanks for your encouraging words - this really should've been reserved for my younger brain...

Just to add on - the End(3) seems rather arbitrary to me. What would the script have been, had I wanted paste the same set of values but instead of pasting at the last data of rows (i.e. B25 in the screenshot), we want to paste at the end of last data of columns (i.e. V11 in the screenshot)?

Appreciate this is a lot to ask. Hope you're having a fine day.

 

[vcoolio]

Hello Kimsrez,

If you wanted to paste the row of values(E3:U3) to V11 instead, you could change the code to this:

Sub Test()

Sheet1.[E3:U3].Copy Sheet1.[V11]

End Sub

However, that would mean that the next data transfer would over-write the previous as V11 is fixed. You could, though, use an InputBox to determine which row to paste into with each data transfer thereafter.

Another option would be:-

Sub Test()

Dim lr As Long: lr = Sheet1.Range("V" & Rows.Count).End(xlUp).Row
If lr < 10 Then lr = 10

Sheet1.[E3:U3].Copy Sheet1.Range("V" & lr + 1)

End Sub

As long as row10 is empty from Column V on, this will paste from row11 down without over-writing previous data.

Cheerio,
vcoolio.

 

[kimsrez]

Hello Kimsrez,

If you wanted to paste the row of values(E3:U3) to V11 instead, you could change the code to this:

Sub Test()

Sheet1.[E3:U3].Copy Sheet1.[V11]

End Sub

However, that would mean that the next data transfer would over-write the previous as V11 is fixed. You could, though, use an InputBox to determine which row to paste into with each data transfer thereafter.

Another option would be:-

Sub Test()

Dim lr As Long: lr = Sheet1.Range("V" & Rows.Count).End(xlUp).Row
If lr < 10 Then lr = 10

Sheet1.[E3:U3].Copy Sheet1.Range("V" & lr + 1)

End Sub

As long as row10 is empty from Column V on, this will paste from row11 down without over-writing previous data.

Cheerio,
vcoolio.

Bless you vcoolio, I cannot thank you enough. So so incredibly helpful - hope you win a lottery!!!

 

[vcoolio]

You're welcome Kimsrez. I'm glad to have been able to assist again.

hope you win a lottery!!!

I'm working on it!

Cheerio,
vcoolio