Transfer range of values to table

Question

Bit of a novice VBA question. I have a range of cells on SheetA - Say $Q$3:$X$300.

That I want to add (paste Values) to the end of a Table - Say Table3.

Can I get some help with the code for this?

I'm newish to VBA - I tried googling for the answer but had no luck.

I believe you need something like `.End(xlDown)`, and you might need to have a look at this other Stackoverflow post "https://stackoverflow.com/questions/10714251"). — Dominique, Jan 02 '23 at 15:53

score 1 · Answer 1 · answered Jan 03 '23 at 15:31

Public Sub Test()

    Dim SourceRange As Range
    Set SourceRange = ThisWorkbook.Worksheets("SheetA").Range("Q3:X3000")
    
    ' - OR -
'    With ThisWorkbook.Worksheets("SheetA")
'        'Looks in column X for last row:
'        Set SourceRange = .Range(.Cells(3, 17), .Cells(.Rows.Count, 24).End(xlUp))
'
'        ' - OR -
'        'Looks in column Q for last row:
'        Set SourceRange = .Range(.Cells(3, 17), .Cells(.Cells(.Rows.Count, 17).End(xlUp).Row, 24))
'    End With
    
    '*************
    'Cells(3, 17) = row 3, column 17 = Q3
    'Cells(.Rows.Count,24) = row 1048576, column 24 = X1048576
    'End(xlUp) = go up to first non-blank cell.
    '*************

    Dim TargetTable As ListObject
    Set TargetTable = ThisWorkbook.Worksheets("SheetB").ListObjects("Table3")
    
    SourceRange.Copy Destination:=TargetTable.DataBodyRange.Offset(TargetTable.DataBodyRange.Rows.Count).Resize(1, 1)
   
End Sub

You're a legend - Thanks mate. – Adrian Buttery Jan 05 '23 at 00:16 — Adrian Buttery, Jan 05 '23 at 00:16

Transfer range of values to table

1 Answers1