#1
|
|||
|
|||
VBA "Run-time error 5" when creating table from region in VBA
Hi there,
I have written a 3-part macro that does a number of things to a database export (an Excel file):
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 |
#2
|
|||
|
|||
never mind!
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 |