Create new sheets from template with new names from list

Hi,

I'm brand new to Macros and need some desperate help after no luck using google.

I have a workbook with a sheet (i.e. a template) I want to copy by clicking a button, and at the same time change the title of the sheet and update one cell in the sheet to reflect the title. I want a new sheet, with a subsequent name to be added upon each click. I have made a list of the sheet names where the intent is that the next name on the list is selected once I click the command button to create a sheet.

So far I have gotten to this, where:
- 'Order sheet template' is the sheet I want to copy
- 'Frontpage' is where I have the list of names stored
- 'A1:A10' is the list of names (which will eventually have to be extended to a longer list)
- G9 is the cell in each new sheet I want to reflect the sheet name


Private Sub CommandButton1_Click()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Order sheet template")
Set sh2 = Sheets("Frontpage")
For Each c In sh2.Range("A1:A10")
sh1.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = c.Value: ActiveSheet.Range("G9") = c.Value
Next
End Sub


This works on everything I want to do, except creating each new sheet upon clicking the command button. I.e.
, when using this code all 10 new sheets appear at once, which will not work for me.

Any thoughts on how I can modify the code to ensure when each time I click the button:
- One new sheet is created as a copy of the template
- The new sheet is renamed as per my list, and for every subsequent click a new sheet is created with the next name in line
- Cell G9 in each new sheet reflects the title of the sheet

There may be other ways of doing this than having a list, so open to other methods. The naming will be 2023-1001, 2023-1002, 2023-1003, etc., i.e. numbers

Appreciate any support!


Regards,
Siggen

Hi,

easier to help with an attachment …
So as missing that means you are enough confident to follow some advises to fix it yourself :
as obviously the loop is useless so remove it !
Before to create the new worksheet check the last worksheet name in order to pick up the next from the list
or just according to the worksheets count, whatever …

Hello Siggen,

May I suggest that you use a Worksheet_SelectionChange event code rather than a code assigned to a button. The result is the same as rather than click on a button, you simply click once on a value in Column A and the relevant new sheet will be created.

Please Login or Register  to view this content.

To implement this code:
- Right click on the "Frontpage" sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears paste the above code.

The code will not duplicate any of the new sheets even if you accidentally click on a cell value which already has a sheet created for it.

Please test the code in a copy of your actual workbook first.

I hope that this helps.

Cheerio,
vcoolio.

try

Please Login or Register  to view this content.

Here's another approach. Although it's easier to assist with an attachment, here is an attempt to meet your requirements. This code checks all the current sheet names and will then create the next template copy with the next name on the list, one per button click.

Please Login or Register  to view this content.

Thank you all for your replies, this is a new world to me, and so nice to get this kind of help from strangers!

The prize goes to maniacb in this round. I made a slight adjustment and now the command button does exactly what I wanted!