easy question - select and copy last row in spreadsheet

[Shyyde]

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

 

[Al Chara]

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.

 

[Shyyde]

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.

 

[Al Chara]

Try the following:

[a1].End(xlDown).Offset(1, 0).EntireRow.Insert Shift:=xlDown

 

[jimmywong98]

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