Copy from one ListObject to another VBA

Question

More a question of writing nice VBA code here, since it technically works. I have a simple task - I need to copy data from one ListObject (2 columns from 4-column table) and add it to the end of another 2-column table (and have the Excel table autoexpand).

I created Range Trans_log, for addressing those 2 columns I need to copy. I'm targeting the newly created ListRow through newrow, in order not to accidentially paste data somewhere in the middle of the table.

However is there a neater way to do this, instead of using With, .Activate and ActiveCell ?

Sub Copy()

Dim newrow As ListRow
Set newrow = ActiveSheet.ListObjects("Log").ListRows.Add

ActiveSheet.Range("Trans_log").Copy

With newrow

.Range(1).Activate
ActiveCell.PasteSpecial xlPasteValues

End With

End Sub

Tim Williams · Accepted Answer · 2019-09-12 23:09:43Z

0

You can do something like this - directly assign the values instead of copy/paste

Sub Copy()

    Dim newrow As ListRow
    With ActiveSheet
        Set newrow = .ListObjects("Log").ListRows.Add()
        newrow.Range.Value = .Range("Trans_log").Value 
    End With
End Sub

EDIT: for copying multiple rows

Sub Copy()
    Dim rw As Range
    Dim newrow As ListRow
    Dim lst As ListObject, sht As Worksheet

    Set sht = Sheets(1)
    Set lst = sht.ListObjects("Log")

    'loop over rows in named range
    For Each rw In sht.Range("Trans_log").Rows
        'only copy rows with data
        If Application.CountA(rw) > 0 Then
            Set newrow = lst.ListRows.Add()
            newrow.Range.Value = rw.Value
        End If
    Next rw
End Sub

edited Sep 12, 2019 at 23:09

answered Sep 11, 2019 at 0:28

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dotsent12 Over a year ago

Thanks Tim. Unfortuantely that reports an error "Application-defined or object-defined error".

Tim Williams Over a year ago

on which line ?

Tim Williams Over a year ago

Tested - works for me provided "Trans_log" is the same shape as the new table row.

dotsent12 Over a year ago

Hi again! Got past the initial error - I was not launching from the same sheet, so adjusting "With ActiveSheet" into "With Sheets(2)" fixed it. Still, a problem persist which you probably also hint at. "Trans_log" is a range of 2 columns (as is Table "Log" - so the data itself fits), however I'm trying to add multiple rows at the same time and was expecting the table to expand as needed. The code does not report any errors anymore, however only adds the 1st row from "Trans_log" range into Table "Log".

Collectives™ on Stack Overflow

Copy from one ListObject to another VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related