Increment Table Names with VBA copy sheet code

[ABennett757]

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

 

[Anthony47]

May be this variant:

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

 

[ABennett757]

May be this variant:

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!