+ Reply to Thread
Results 1 to 6 of 6

Wanted: Highlight last line of text and copy to line below

  1. #1
    Registered User
    Join Date
    10-04-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    13

    Wanted: Highlight last line of text and copy to line below

    Hi,

    I have a spreadsheet where on a weekly basis data is copied in to various tabs. I then have a "formula" tab where I have a single line of formulas which look up the various data tabs and extract the results I want to show.

    Currently each week, before I import the new data into the various tabs, I copy the last row in the "formula" tab and paste to the line below it. This contains all the working formulas. I then paste values only on the line that I copied, thus "locking in" the values it calculated with that weeks data. This means that each row then contains the results with that week's data, and this will grow week by week.

    What I am looking for is a formula that automates this process. So let's say that row 30 is the final row of data on my formula tab, it contains the formulas I want to use. I would need a macros that does the following:

    1. Looks up last row (row 30)
    2. Copies last row (row 30)
    3. Pastes to next empty row (row 31)
    4. Pastes values only to second last row (row 30)

    I'm not too great with writing macros, I've found plenty that can find the last row, but I can't get them to work to highlight that row.

    I hope this makes sense. Any help appreciated.

    Thanks
    KC

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Wanted: Highlight last line of text and copy to line below

    Maybe:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-04-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Wanted: Highlight last line of text and copy to line below

    Great, i've used the following lines:

    Please Login or Register  to view this content.
    This does exactly what I want. I have two questions:

    1. I have the columns coloured but this is not copying the colours, can these be copied too?
    2. Can you provide a quick explanation on how this code works? I've not seen anything quite like it it terms of simplicity for what it's doing. For example I can't see where the post commands are.

    Thanks for your reply

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Wanted: Highlight last line of text and copy to line below

    When I tested it for the code you used in Post#3, it copied the colours? If however if you are using conditional formatting to get you're column coloring, then no the code cannot be amended for that.

    Rows(Range("A" & rows.count).End(3)(1).Row).copy selects the last row with data in Column A and copies it too the row beneath
    Which would be Range("A" & rows.count).End(3)(2) (the first row without data).

    Now the last used row with data becomes the row that was just pasted becomes (1).

    The row which was just copied becomes (0) - .value = .value takes out the formulas from 1 row above the last used row.

    These are all offsets (3) - is a shortcut for (xlUp)

    The other values are offset commands (shorter version)

    .End(3)(1).Row - last row with data
    .End(3)(2).Row - the first row without data
    .End(3)(0).Row - One row above the last row with data

    Hope that helps.

  5. #5
    Registered User
    Join Date
    10-04-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Wanted: Highlight last line of text and copy to line below

    Yes- the colour formatting was an issue at my end.

    Thanks for everything John, this is a great help.

  6. #6
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Wanted: Highlight last line of text and copy to line below

    You're welcome. Glad to help out and 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. [SOLVED] 2 Line Entry Checkbook - Highlight Consecutive Rows Based on Text
    By nesbensen in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-14-2012, 11:08 AM
  2. Copy pasting from Excel issue - NEW LINE/LINE BREAK
    By denneboom in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-10-2012, 10:18 AM
  3. Copy pasting from Excel issue - NEW LINE/LINE BREAK
    By denneboom in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 09-07-2012, 06:41 AM
  4. Copy pasting from Excel issue - NEW LINE/LINE BREAK
    By denneboom in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-07-2012, 06:40 AM
  5. Replies: 6
    Last Post: 07-12-2005, 04:05 PM

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