VBA to copy last table row specific column range to new row at bottom of table

[Doug Mutzig]

Good morning all!

I have been looking for answer on the forums and other sites but not found anything that works. I have a table (MBU) with 36 columns that starts at A3. I would like to have a button that automatically finds that last row, copys all the information (forumla, formating, etc.) from columns A:E and then pasts this as a new row at the end of the table.

I found the following code on this site: Contextures

Sub CopySelectionVisibleRowsEnd()
Dim ws As Worksheet
Dim mySel As Range
Dim lRow As Long
Dim lRowNew As Long
Dim lRowsAdd As Long
Dim myList As ListObject
Dim myListRows As Long
Dim myListCols As Long

Set ws = ActiveSheet
Set mySel = Selection.EntireRow
Set myList = ActiveCell.ListObject
myListRows = myList.Range.Rows.Count
myListCols = myList.Range.Columns.Count
lRow = ws.Cells.Find(What:="*", _
        SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, _
        LookIn:=xlValues).Row + 1

mySel.SpecialCells(xlCellTypeVisible).Copy
ws.Cells(lRow, 1).PasteSpecial Paste:=xlPasteAll

lRowNew = ws.Cells.Find(What:="*", _
        SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, _
        LookIn:=xlValues).Row + 1
lRowsAdd = lRowNew - lRow

With myList
    .Resize ws.Range(.Range.Resize(myListRows + lRowsAdd, myListCols).Address)
End With

Application.CutCopyMode = False
End Sub

I was able to limit the copy to the range A:E buy changing the line

Set mySel = Selection.EntireRow

to

Set mySel = Selection.Range ("A3:E3")

however, you have to select a cell in the table for the copy/paste to work and it copies the info from the selected cell.

Is there a way to modify this code to automaticaly select the last row of the table, copy A:E, and paste as a new row at the bottom of the table?

Thank you very much for any help and guidance you can give me on this!

 

[Yongle]

This copies the first 5 cells of last row of the table to the row below the table

Sub CopyRow()
    Dim rng As Range
    With ActiveSheet.ListObjects(1)
        Set rng = .Range.Offset(.Range.Rows.Count - 1).Resize(1, 5)
        rng.Copy rng.Offset(1)
    End With
End Sub

There is no requirement to find the last row (unless your table contains empty rows at the bottom)

 

[Doug Mutzig]

That works perfectly! Thank you so much!!