nirvehex
05-06-2015, 11:37 AM
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
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