Copy Table from one Sheet to Another

[nirvehex]

Hello,

I'm trying to use VBA to copy Table2 from Sheet1 to Sheet2. The only problem is each time I do this, the table is named differently so my VBA code breaks down.


'Copies Table2 to Sheet2

Sheets("Sheet1").Select
Range("Table2[#All]").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste

'Inserts Column and Inserts Calculation


Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("Table22[[#Headers],[Column1]]").Select
ActiveCell.FormulaR1C1 = "Calculation"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=5*5/.48"
Range("C4").Select


The first time it pasted the table as Table22 which I was I named it such in my code. However, after I deleted it and ran it again it named it Table27. And if I delete it and repaste it again, it's called something else. How do I adjust the code to either name it the same thing every time or account for a variable name change?

Thanks!

[nirvehex]

Nevermind...found a work around.

[SamT]

You can't have the same range Name on two sheets.

Unless... You precede the Range Name with the Sheet designator, 'Sheet1'! at the time of creation.
You should use single quotes around the designator.

Sheets("Sheet1").Range("'Sheet1'!Table2[#All]").Copy Sheets("Sheet2").Range("A2")

12669

I am not familiar with the structure
Table2[#All]So cannot comment on its usage.