Increment Table Names with VBA copy sheet code

ABennett757

New Member
Joined
Mar 25, 2021
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello, I current have the below VBA code implemented to copy a worksheet the number of times specified by the user and add to the end of the same workbook. This is working well, except that there is a table in my worksheet and each time it is copied the table name adds another digit. For instance, the name of the first table being copied is "Table1". If I make 10 copies of the worksheet with "Table 1" the table name in the 10th copy will be "Table1111111111". This is causing the size of my Excel file to increase exponentially especially in scenarios where I may have 50+ worksheets (that would result in a table name with 50x1's). Any thoughts on how I could modify my current code to increment the table number within each new copy as opposed to having another digit added? For instance, if I made 50 copies, the final table would be Table51. Thanks in advance for your thoughts!

Public Sub SystemSheet_Copy()
Dim n As Integer
On Error Resume Next
n = InputBox("How many copies of this sheet do you want? Note: update system list on Project Summary after renaming new system tabs")

If n >= 1 Then
For numtimes = 1 To n
ActiveSheet.Copy After:=ActiveWorkbook.Sheets(Worksheets.Count)
Next
End If
End Sub
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
May be this variant:
VBA Code:
StartName = ActiveSheet.ListObject(1).Name
For numtimes = 1 To n
    ActiveSheet.Copy After:=ActiveWorkbook.Sheets(Worksheets.Count)
    ActiveSheet.ListObjects(1).Name = StartName & "_" & numtimes
Next
In this way, if the copied table is named Table1 it get renamed Table1_1, Table1_2 and so on
Of course if the initial name is Table1_1, then it will be renamed Table1_1_1, Table1_1_2, Table1_1_3, and so on

Bye
 
Solution

ABennett757

New Member
Joined
Mar 25, 2021
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
May be this variant:
VBA Code:
StartName = ActiveSheet.ListObject(1).Name
For numtimes = 1 To n
    ActiveSheet.Copy After:=ActiveWorkbook.Sheets(Worksheets.Count)
    ActiveSheet.ListObjects(1).Name = StartName & "_" & numtimes
Next
In this way, if the copied table is named Table1 it get renamed Table1_1, Table1_2 and so on
Of course if the initial name is Table1_1, then it will be renamed Table1_1_1, Table1_1_2, Table1_1_3, and so on

Bye
I can work with that, thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,751
Messages
5,638,154
Members
417,010
Latest member
jnuss03

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