[VBA] copying table from one sheet to another and naming mess

ursua

New Member
Joined
Jan 9, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hi,
So i have a workbook, two tables in it. in sheet1 there is a table called Table1, in sheet2 Table2. I want to copy Table2 and here's a code:

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

and it copies and pastes the Table2. Problem that i have is that i want to do something else with that pasted table and VBA keeps changing the pasted table names like Table21, Table 24.
Is there a way to name that pasted table to have always the same name (maybe it is worth mentioning here that Table2 has different numbers of rows depending on day the data is fetched)?
Thanks in advance.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
You can dynamically capture the name of the new table after you paste it. Then, since it is stored as a variable, you can refer to this table by using this variable in your VBA code.

That would look something like this:
VBA Code:
    Dim newTbl As String

    Sheets("Sheet2").Select
    Range("Table2[#All]").Copy
    Sheets("Sheet1").Select
    Range("T2").Select
    ActiveSheet.Paste
    newTbl = Range("T2").ListObject.Name
  
    MsgBox newTbl

If you did want to rename the new Table to "Table2", you can do that by adding the following line at the end of my code:
VBA Code:
    ActiveSheet.ListObjects(newTbl).Name = "Table2"
 
Last edited:
Upvote 0
Solution
Note the edit I made to my previous post.
 
Upvote 0
You are welcome.
Glad I was able to help.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top