VBA "Run-time error 5" when creating table from region in VBA

[le_robert]

Hi there,


I have written a 3-part macro that does a number of things to a database export (an Excel file):

• Part 1: pastes in the data and preps it for part 2, turns it into an Excel table & copies part of it
• Part 2: opens Word and does some wildcard searches to do some complex text reordering
• Part 3: pastes back into Excel and tidies up table

My problem is that I get a Run-time error 5 at the table creation stage in Part 1 (see code below), but this is only when the macro pastes the export into my file. If I paste in manually (default paste) and run the macro, it runs fine. It doesn't matter how I ask it to paste (.Cells(1, 1).PasteSpecial / ActiveSheet.Paste), I get the same error. Am I missing something by adding this one extra line of code?

Thanks in advance!

Sub COM_exp_stp1()

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

Set src = Range("A1").CurrentRegion
Set ws = ActiveSheet

With ws
    .Cells(1, 1).PasteSpecial
    .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

[le_robert]

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.

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