+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    01-20-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    4

    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.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    10,155

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

    Welcome to the board
    How about
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-20-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    4

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

    Quote Originally Posted by Fluff13 View Post
    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.

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    10,155

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

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

  5. #5
    Registered User
    Join Date
    01-20-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    4

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

    Quote Originally Posted by Fluff13 View Post
    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"

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    10,155

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

    How about
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    01-20-2018
    Location
    England
    MS-Off Ver
    2016
    Posts
    4

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

    Quote Originally Posted by Fluff13 View Post
    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!

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    10,155

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

    Glad to help & thanks for the feedback

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Paste copied cell range into multiple userform textboxes
    By c00ly81 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-11-2021, 01:30 PM
  2. Paste copied range into added worksheet
    By beancounter72213 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-02-2014, 02:16 PM
  3. [SOLVED] Paste previously copied values as 123 except last one and...
    By intex in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-30-2014, 04:45 PM
  4. [SOLVED] start from bottom, find first instance of "Page", paste copied range below it...possible?
    By fredderf81 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-02-2013, 02:02 PM
  5. [SOLVED] VBA Code to Paste copied range as a picture
    By djfatboyfats in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-21-2012, 01:51 AM
  6. looking for an empty row to paste a range of copied cells
    By jayers in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-11-2009, 10:12 PM
  7. [SOLVED] copied formulas paste as values
    By Trev in forum Excel General
    Replies: 2
    Last Post: 02-11-2006, 01:15 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1