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