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

knepster

New Member
Joined
Aug 3, 2012
Messages
3
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

Well-known Member
Joined
Jul 17, 2007
Messages
1,051
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?
 

knepster

New Member
Joined
Aug 3, 2012
Messages
3
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

New Member
Joined
Aug 3, 2012
Messages
3
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.
 

Warship

Well-known Member
Joined
Jul 17, 2007
Messages
1,051
Code:
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
 

Forum statistics

Threads
1,085,031
Messages
5,381,303
Members
401,733
Latest member
Kabasa007

Some videos you may like

This Week's Hot Topics

Top