Code for a button to add sheet duplicated from existing sheet and auto-name from sequential list?

[knepster]

Seems complex, but can a single button code; 1) add a new sheet (preferably at the front of the worksheet list), 2) the new sheet will be a duplicate of an existing sheet, 3) the new sheet will be auto-named based on the next sequential cell from a list on a master-data sheet in the same workbook. Windows 7, Excel 2010.
Thank you.

 

[Warship]

Describe the Name list.
Is it only thing on that sheet?
In Col A? Row 1? Grid?
Can we delete from the list used Names?
Or use other columns for tracking used names?
Are all the Names valid sheet names?

What is the name of the template sheet?

 

[patel45]

a link to your file will be helpful

 

[knepster]

Describe the Name list.
Is it only thing on that sheet?
In Col A? Row 1? Grid?
Can we delete from the list used Names?
Or use other columns for tracking used names?
Are all the Names valid sheet names?

What is the name of the template sheet?

I'm designing on paper at this point the code is all conceptual. The "Name list" will be a column of sequential numbers that will be used as the titles of the new sheets that are auto-generated on the button click. The code should know which is the next sequential number because the cell to the right of it will be blank. The "Name list" column will be the first column on a table of multiple columns/rows of data on a sheet called "Data Sheet." Since I haven't designed the "Data Sheet" yet - it would be fine to use Col A for the "Name list." Names cannot be deleted from the "Name list" because theyll continue to hold historical data. We can call the template sheet anything we want - "Template Sheet" sounds fine. Thanks.

 

[knepster]

a link to your file will be helpful

Sorry, I'm a new user and not used to the protocol. At this point the design is all on paper so I have no code to share and no link to attach. All I've done is insert a button with no functional coding yet. My previous reply to Warship has some more detail. Thank you.

 

[patel45]

I don't want see your code, but your workbook

 

[Warship]

Sub NewSheet()
    Dim shDATA As Worksheet
    Dim shTEMP As Worksheet
    Dim shNAME As String
    
    Set shDATA = Sheets("Data")
    Set shTEMP = Sheets("Template")
    
    shNAME = shDATA.Cells(Rows.Count, "B").End(xlUp).Offset(1, -1)
    shTEMP.Copy Before:=Sheets(1)
    ActiveSheet.Name = shNAME
End Sub