Find last used row in a range, paste it 1 row down, paste copied row as values

Hi all,

So I've scoured the internet the last few evenings trying to find a solution for what I'm trying to achieve, to no avail so here i am.

I'm a complete VBA beginner so I've found it difficult to translate similar problems into my own solution, i am trying though!

Basically:

I have Formulas in L2:Y2

I would like these to be copied and pasted into L3:Y3

Then i would like to formulas in L2:Y2 to be pasted as values.

And that is the code finished.

The next time i run the VBA it will find the last used row in that range, which will be L3:Y3, so it will copy L3:Y3 and paste it into L4:Y4 so i need the code to find the last used row in that range each time.

Hopefully someone can help as I'm pulling (what's left of) my hair out at the minute.

I've attached an example of the sheet which already has L2:Y4 used so you can see where I'm coming from.

Thanks.

Welcome to the board
How about

Please Login or Register  to view this content.

Originally Posted by Fluff13

Welcome to the board
How about

Please Login or Register  to view this content.

Thank you for your reply, this worked perfectly!! And the code looks so much simpler then what i was attempting before.

Would it be possible to run the same code for a number of different worksheets in the workbook on the same macro? I basically have to run this on 14 different worksheets.

Easily done.
Does it need to be run on all sheets, first 14 sheets, last 14 sheets, or specific named sheets?

Originally Posted by Fluff13

Easily done.
Does it need to be run on all sheets, first 14 sheets, last 14 sheets, or specific named sheets?

It's specific sheets, namely;

"SHEET5", "SHEET6", "SHEET7", "SHEET8", "SHEET9","SHEET10", "SHEET11", "SHEET12", "SHEET13","SHEET14", "SHEET15", "SHEET16","SHEET18", "SHEET19"

How about

Please Login or Register  to view this content.

Originally Posted by Fluff13

How about

Please Login or Register  to view this content.

Absolutely spot on, thank you very much you've made my night! Time for a drink i think now!

Glad to help & thanks for the feedback