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

ursua

New Member
Joined
Jan 9, 2020
Messages
10
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
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:
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
Note the edit I made to my previous post.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad I was able to help.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,036
Members
416,007
Latest member
csf

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
Top