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

le_robert · #1 07-20-2022, 05:18 AM

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!

Code:

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 · #2 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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
ADODB import of Excel data results in "Run-time error -2147467259: <sheet> is not a valid name."	misaak	Excel Programming	3	06-20-2016 07:55 AM
Error: "Changes made were lost...reconnect with server", when switching "from" field	randhurrle	Outlook	2	02-25-2015 06:51 PM
Creating "odd shaped" Table	norwood	Word VBA	1	03-27-2014 05:08 PM
Error Outlook 2003 " Receive much mail the same at the same time"	nhat2nhat2	Outlook	1	09-26-2011 11:10 PM
How to edit the "Format" and the "show level" of an EXISTING table of content?	Jamal NUMAN	Word	2	08-14-2011 10:46 AM