How to create a new table out of pasted values VBA

Question

I try to make a table out of new range which has just been pasted (it is an extraction of old table but only part of it, not entire so it doesn't paste as a table) in the worksheet, however I got an error. It used to even create a table with this error 5 - invalid procedure or call

Set RngTable = ThisWorkbook.Worksheets("Sheet1").Range("A1:D5")
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set DestRange = Cells(LastRow + 3, "A")

RngTable.Copy
DestRange.PasteSpecial xlPasteAll

DestRange.CurrentRegion.Select

Set newtbl = ActiveWorkbook.Sheets("Sheet1").ListObjects.Add(xlSrcRange, DestRange.CurrentRegion.Select, , xlYes)

Wow.. it works, thank you. Perhaps have you idea why if I copy and paste from the same worksheet, the column and row width and heigh stays the same, but if I copied worksheet and I copy the table from this new copied worksheet to the original one, these parameters are not as they were in the table? — lifeofthenoobie, Dec 01 '21 at 14:32
I'm not sure. Do you see the same issue if you do the copy/paste manually? — BigBen, Dec 01 '21 at 14:34
Exactly, same issue. Whats weired, that in this copied original worksheet, If i copy and paste this formatted piece of table into same worksheet, it still loses parameters of column & row width and height. Turns out it works as intendedONLY in this one, original worksheet — lifeofthenoobie, Dec 01 '21 at 14:37

VBasic2008 · Answer 1 · 2021-12-01T14:59:24.097

Copy Range to a New Table

One Worksheet

Option Explicit

Sub CopyToNewTable()
    
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    Dim srg As Range: Set srg = ws.Range("A1:D5")
    Dim slCell As Range: Set slCell = ws.Cells(ws.Rows.Count, "A").End(xlUp)
    
    Dim dfCell As Range: Set dfCell = slCell.Offset(3)
    
    ' Resize to the size of the source.
    Dim drg As Range: Set drg = dfCell.Resize(srg.Rows.Count, srg.Columns.Count)

    srg.Copy drg
    ' ... or if only values is an option, more efficient is:
    'drg.Value = srg.Value 
           
    Dim dtbl As ListObject
    Set dtbl = ws.ListObjects.Add(xlSrcRange, drg, , xlYes)
    
End Sub

Two Worksheets

Sub CopyToNewTable2Worksheets()
    
    Dim sws As Worksheet: Set sws = ThisWorkbook.Worksheets("Sheet1")
    Dim srg As Range: Set srg = sws.Range("A1:D5")
    
    Dim dws As Worksheet: Set dws = ThisWorkbook.Worksheets("Sheet2")
    Dim dfCell As Range
    Set dfCell = dws.Cells(dws.Rows.Count, "A").End(xlUp).Offset(3)
    
    ' Resize to the size of the source.
    Dim drg As Range: Set drg = dfCell.Resize(srg.Rows.Count, srg.Columns.Count)

    srg.Copy drg
    
    Dim dtbl As ListObject
    Set dtbl = dws.ListObjects.Add(xlSrcRange, drg, , xlYes)
    
End Sub

How to create a new table out of pasted values VBA

1 Answers1

Copy Range to a New Table