Microsoft Office Forums - View Single Post - [Solved] VBA "Run-time error 5" when creating table from region in VBA

le_robert · 07-20-2022, 05:23 AM

5 seconds after posting, I realised what I'd done wrong: I had defined the table source region (src) before pasting, which presumably caused some confusion when it came to creating the table. I swapped the order around and it seems to be working.

Code:

Sub COM_exp_stp1()

Dim src As Range
Dim ws As Worksheet
Dim tbl As ListObject

Set ws = ActiveSheet

ws.Paste

Set src = Range("A1").CurrentRegion

With ws
    .Cells(Rows.Count, "A").End(xlUp).EntireRow.Delete
    .Pictures.Delete
    .Rows("1:3").EntireRow.Delete
    .Columns(1).Copy
    .Columns(3).Insert
    .Columns(1).NumberFormat = "0000"
    .Columns(3).NumberFormat = "0000"
    .ListObjects.Add(SourceType:=xlSrcRange, Source:=src, _
    xlListObjectHasHeaders:=xlYes, tablestyleName:="TableStyleLight11").Name = "Table1"
End With

Set tbl = ws.ListObjects(1)

tbl.DataBodyRange.Columns("A:D").Copy

End Sub