Table Changing Name After Macro

[nirvehex]

Hi,

I have two tables. Table2 and Table28.

I'm running a macro that copies Table2 and pastes it on top of Table28. It then goes to insert a column in Table28, but Table28 has since changed its name to Table23 thus destroying all my references to it as Table28. But if I paste Table2 as values it completely erases the Table28. All I'm trying to do is paste Table 2 into Table28 and have the table names stay the same.

Here's my code:


Sub Add()


Application.ScreenUpdating = False


'Copies Pre Cleaned Export


Sheets("Export").Select
Range("Table2[[#Headers],[ServiceId]]").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Add Export").Select
Range("Table28[[#Headers],[ServiceId]]").Select
ActiveSheet.Paste


'Inserts Column and Calculates Additions (when it inserts the column, the column takes the generic header name, "Column1" and then it's changed to "Monthly Addition"


Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("Table2[[#Headers],[Column1]]").Select
ActiveCell.FormulaR1C1 = "Monthly Addition"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=[@Length]*[@Width]*[@Height]*12"
Range("C4").Select




I think it has something to do with how I'm pasting it, because as soon as I paste Table2 onto Table28, thats when Table28 gets its Table name changed to Table23. How do I keep these names intact?

Thank you!

Note this is cross posted at http://www.mrexcel.com/forum/excel-questions/853430-table-changing-name-after-macro.html

[mancubus]

i assume topleft cell of tables are A1



Dim loTable2 As ListObject, loTable28 As ListObject
Set loTable2 = Worksheets("Export").ListObjects("Table2")
Set loTable28 = Worksheets("Add Export").ListObjects("Table28")

loTable2.Range.Copy
loTable28.Range.PasteSpecial xlPasteValues 'this will convert table to range

Worksheets("Add Export").ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = "Table28" 'restore table

With Worksheets("Add Export").ListObjects("Table28")
.ListColumns.Add Position:=3
.HeaderRowRange(3) = "Monthly Addition"
.ListColumns(3).DataBodyRange.Cells(1).Formula = "=[@Length]*[@Width]*[@Height]*12"
End With