Macro that copy and pastes last used row under last used row

[paytoplay]

This is my macro so far. It copies and pastes the last used row under the last used row. I just want it to copy and paste the last row of A-I, but messes up when I try to input it.

Dim lRow As Long

ThisWorkbook.Activate
lRow = Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, 1).End(xlUp).Row
Range("A" & lRow).Copy
lRow = lRow + 1
Range("A" & lRow).PasteSpecial
Application.CutCopyMode = False
End Sub



Thanks

 

[L. Howard]

Here are three similar examples to copy the last row to the next empty row.

I used Sheet1, you will change to your sheet name. A couple Msgbox lines for added info, uncomment lines to see Msgbox's

Howard

Option Explicit


Sub Copy_Last_Row_1()

  Sheets("Sheet1").Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).EntireRow.Copy
  Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False
End Sub



Sub Copy_Last_Row_2()

Dim sh As Worksheet, rng As Range, lRow As Long, LCol As Long
Set sh = Sheets("Sheet1")

LCol = sh.Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlPrevious).Column
lRow = sh.Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlPrevious).Row

'MsgBox "Last row is " & lRow & " " & "Last column is " & LCol

sh.Range(sh.Cells(lRow, 1), sh.Cells(lRow, LCol)).Copy
Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False
End Sub



Sub Copy_Last_Row_3()

Dim sh As Worksheet
Dim lastColumn As Integer
Dim lastRow As Long
Set sh = Sheets("Sheet1")

 lastRow = ActiveSheet.Range("A1").End(xlDown).Row
 lastColumn = ActiveSheet.Range("A" & lastRow).End(xlToRight).Column
 
'MsgBox lastRow & " " & lastColumn

sh.Range(sh.Cells(lastRow, 1), sh.Cells(lastRow, lastColumn)).Copy
Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

 Application.CutCopyMode = False
End Sub

 

[My Aswer Is This]

Try this:

Sub Copy_Lastrow()
Rows(Cells(Rows.Count, "A").End(xlUp).Row).Copy Rows(Cells(Rows.Count, "A").End(xlUp).Row + 1)
End Sub

 

[My Aswer Is This]

If you only want to copy values you could use this:

Sub Copy_Lastrow()
Rows(Cells(Rows.Count, "A").End(xlUp).Row).Resize(2).Value = Rows(Cells(Rows.Count, "A").End(xlUp).Row).Value
End Sub