Copy last row from one table and paste to the last row of another table in the same worksheet

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
184
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I need to create a macro that will copy the last row of the GroupData (TO Data tab) table and paste it to the last row of the ArchiveTOData (Archived TO Data tab) however I can't figure out how to not make it copy the entire table. Obviously the last row for both tables will be dynamic as the row # will change each month.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Have a look at the following post . . .


Hope this helps!
 
Upvote 0
Have a look at the following post . . .


Hope this helps!
Thank you
 
Upvote 0
You're very welcome, cheers!
I apologize but there is a minor tweak that I forgot. I am actually wanting to copy the last 5 rows of the table (all our Business Divisions) each month. How can I tweak the range to ensure it grabs the bottom 5 rows every time? Thank you again
 
Upvote 0
In that case, try...

VBA Code:
Sub CopyToOtherTable()
 
    With ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")
        Dim sourceRange As Range
        If .ListRows.Count > 5 Then
            Set sourceRange = .ListRows(.ListRows.Count).Range.Offset(-4).Resize(5)
        ElseIf .ListRows.Count > 0 Then
            Set sourceRange = .DataBodyRange
        Else
            MsgBox "No data is available!", vbExclamation 'optional
            Exit Sub
        End If
    End With
 
    With ThisWorkbook.Worksheets("Sheet2").ListObjects("Table2")
        Dim newListRow As ListRow
        Set newListRow = .ListRows.Add
    End With
 
    sourceRange.Copy
    newListRow.Range.PasteSpecial xlPasteValues
 
    Application.CutCopyMode = False
 
End Sub

Hope this helps!
 
Upvote 0
Solution
In that case, try...

VBA Code:
Sub CopyToOtherTable()
 
    With ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")
        Dim sourceRange As Range
        If .ListRows.Count > 5 Then
            Set sourceRange = .ListRows(.ListRows.Count).Range.Offset(-4).Resize(5)
        ElseIf .ListRows.Count > 0 Then
            Set sourceRange = .DataBodyRange
        Else
            MsgBox "No data is available!", vbExclamation 'optional
            Exit Sub
        End If
    End With
 
    With ThisWorkbook.Worksheets("Sheet2").ListObjects("Table2")
        Dim newListRow As ListRow
        Set newListRow = .ListRows.Add
    End With
 
    sourceRange.Copy
    newListRow.Range.PasteSpecial xlPasteValues
 
    Application.CutCopyMode = False
 
End Sub

Hope this helps!
You are a GENIUS! Thank you
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top