easy question - select and copy last row in spreadsheet
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: easy question - select and copy last row in spreadsheet

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a pretty easy and quick question. I'm trying to write a macro that copies the last row in a spreadsheet, and then pastes the last row. This will be used for an "Add New Record" button that I am assigning the macro to. the idea is that if the last row is always a "blank row" that has the formulas, then as long as the user hits the "Add New Record" button, the formulas will be in the row for the New Record.

    Here's what I have now:

    Sub test()
    '
    ' test Macro
    ' Macro recorded 4/10/2002 by Ryan M. Cavaliere
    '

    '
    Sheets("Env Site Assessments").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    Rows("49:49").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.Insert Shift:=xlDown
    End Sub


    The problem is that Row 49 is being copied, not the last row. I just need to know the correct syntax to select the last row. I thought it was xlLastRow, but that doesn't seem to work.

    If someone could take a minute to answer this email, it would be greatly appreciated.

    Thanks!

    Ryan Cavaliere



  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following code:

    [a1].End(xlDown).EntireRow.Select

    It will select the last row as long as column A contains the extent of your data.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Al,

    Thank you much for your prompt answer.

    I have a spreadsheet with columns A through V. The code that you posted comes close to what I want, but it doesn't quite work. I want to copy a "blank" row at the bottom, only the row is not really blank - it contains formulas. Using the code that you posted, I am copying the last row that has information in column A. Actually, I want to copy a row below that - a row that only has the formaulas in the cells, but that's it.

    What is the syntax to select the last row, and then to copy and insert the copied row, thereby moving the last row down one row?

    I want to use this so that the user just hits an "Add New Record" button.



    On 2002-04-10 06:54, Al Chara wrote:
    Try the following code:

    [a1].End(xlDown).EntireRow.Select

    It will select the last row as long as column A contains the extent of your data.

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following:

    [a1].End(xlDown).Offset(1, 0).EntireRow.Insert Shift:=xlDown
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  5. #5
    New Member
    Join Date
    Nov 2011
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy latest row in file A and paste into latest row in file B

      
    Dear Sir

    I have been try to write a VBA code in the macro to copy the latest row in file A and wanna to paste them in file B latest row.

    can pls kindly advise what is the suitable code which i can use to execute the above mention function?


    Merry Christmas & Happy new year!
    Many THanks!


    Jim

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com