Copy a Sheet, Rename Sheet, Rename Table on Sheet using a Range

[arossijr]

Good Evening All or Good Day depending on your time zone...

I have a workbook that I am setting up as a Template, I have 3 Template Sheets in the Workbook. They all have 1 Table located on them.
I have a basic VBA Code to Copy the Template, Rename the Template but I cannot get or find a way to Rename the Tables on the New Sheets.
The Code I have is:

Sub CopyTempWeeklySheets()
Dim sh1 As Worksheet, sh2 As Worksheet, C As Range
Set sh1 = Sheets("Template Weekly")
Set sh2 = Sheets("Controls 2")
  For Each C In sh2.Range("F44", sh2.Cells(Rows.Count, 6).End(xlUp))
    sh1.Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = C.Value
    Next
    
End Sub

What I was hoping to do was to add in ListObjects.Name=D.Value
An set that up as part of the "For" section like it does for "C"

I have this Code listed 3 times, 1 for Each Template. Mainly because it didnt stop when i set the Range like this E1:E10...
Where did I make the left turn toward Albuquerque?

 

[My Aswer Is This]

Would something like this not work:
It copies the sheet named Template
Gives it the name "Me" and names the Table "Me"

Just a example of course just change the names.

This assumes you only have one table in Template sheet.

Try this:

Sub Copy_Sheet()
Application.ScreenUpdating = False
'Modified 10/22/2018 5:22 PM  EDT
Sheets("Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Me"
ActiveSheet.ListObjects(1).Name = "Me"
Application.ScreenUpdating = True
End Sub

 

[arossijr]

This might get me started...
The First Macro/VBA Copies the Weekly Template Sheet 15 times, Renames them based on a Table/Range on Controls 2 sheet. When this Template is opened they change Certain Criteria and it changes the "Sheet Names" that are in the range to be named when they make or run the Macro to Copy Weekly Template..
I then have 15 Table Names that have to go with that sheet...
Ex: Sheetname "Pay Sales 01-01-18 to 01-7-18" The Table would be Named NrwdMnth1Week1
So the Sheetnames and Table Names have to coincide so that the Formulas will work...

 

[arossijr]

Ok So I modded my code a bit, Now I am getting a runtime error 1004 stating that you cant use a name already in use...

Sub CopyTempMonthlySheets()
Dim sh1 As Worksheet, sh2 As Worksheet, C As Range, D As Range
Set sh1 = Sheets("Template Monthly")
Set sh2 = Sheets("Controls 2")
  For Each C In sh2.Range("G44", sh2.Cells(Rows.Count, 7).End(xlUp))
  For Each D In sh2.Range("B59", sh2.Cells(Rows.Count, 2).End(xlUp))
    sh1.Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = C.Value
    ActiveSheet.ListObjects(1).Name = D.Value
    Next
    Next
    
End Sub

It stops at the ActiveSheet.Name=C.Value... BUT if did create/Copy 1 time and named the Table Correctly... But it is stopping on the C Range which is for naming the Sheets...

 

[My Aswer Is This]

Well I think it would have to be like this:

Sub CopyTempMonthlySheets()
Dim sh1 As Worksheet, sh2 As Worksheet, C As Range, D As Range
Set sh1 = Sheets("Template Monthly")
Set sh2 = Sheets("Controls 2")
  For Each C In sh2.Range("G44", sh2.Cells(Rows.Count, 7).End(xlUp))
  For Each D In sh2.Range("B59", sh2.Cells(Rows.Count, 2).End(xlUp))
    sh1.Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = [COLOR=#ff0000]sh2[/COLOR].C.Value
    ActiveSheet.ListObjects(1).Name = [COLOR=#ff0000]sh2[/COLOR].D.Value
    Next
    Next
    
End Sub

Note code marked in Red

 

[My Aswer Is This]

Your original post mentioned nothing about wanting to copy the Template 15 times

And you said there was one Table on the template sheet.

See here:

I have a workbook that I am setting up as a Template, I have 3 Template Sheets in the Workbook. They all have 1 Table located on them.
I have a basic VBA Code to Copy the Template, Rename the Template but I cannot get or find a way to Rename the Tables on the New Sheets.
The Code I have is:​

 

[arossijr]

My Answer Is This, I apologize for that misunderstanding... I had thought that was in there... my bad...
I tried the modified code adding the sh2 to the 2 lines that you suggested... unfortunately it errors out...
If i take out the sh2 it will copy the sheet once and change the table name then errors out telling me that you cannot have 2 sheets the same name...
I am thinking of trying to change the Active sheet back to sh2....
Thank you for taking the time out to assist - Always, Always appreciated!!

 

[My Aswer Is This]

I like helping and learning more myself every day.

So I went back and looked at your post.

In post number one you had this:

[LEFT][COLOR=#333333][FONT=monospace]Sub CopyTempWeeklySheets()
Dim sh1 As Worksheet, sh2 As Worksheet, C As Range
Set sh1 = Sheets("Template Weekly")
Set sh2 = Sheets("Controls 2")
  For Each C In sh2.Range("F44", sh2.Cells(Rows.Count, 6).End(xlUp))
    sh1.Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = C.Value
    Next
    
End Sub[/FONT][/COLOR][/LEFT]

But then in Post 4 you had this:

[LEFT][COLOR=#333333][FONT=monospace]Sub CopyTempMonthlySheets()
Dim sh1 As Worksheet, sh2 As Worksheet, C As Range, D As Range
Set sh1 = Sheets("Template Monthly")
Set sh2 = Sheets("Controls 2")
  For Each C In sh2.Range("G44", sh2.Cells(Rows.Count, 7).End(xlUp))
  For Each D In sh2.Range("B59", sh2.Cells(Rows.Count, 2).End(xlUp))
    sh1.Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = C.Value
    ActiveSheet.ListObjects(1).Name = D.Value
    Next
    Next
    
[/FONT][/COLOR][/LEFT]

Why are you changing the ranges?

First you had starting in F44

But then you changed it to G44

And why can we not put sheet name in like F44 and below
And table name Stating in G44 and below

You have in your last post

G44 and below and

B59 and below

You must have a lot going on in this sheet.

I'm actually surprise we cannot just start on A1 and below And B1 and below

You do not have a empty sheet where we can do this?​

 

[My Aswer Is This]

I also have a question about Tables. Does your Template have one Table or multiple Tables?

And if more then one how many?