Please help. Macro for creating a specific number of sheets

[alelea91]

Hi.

I need to create a large amount of sheets in Excel. Please help me by posting a macro that can do this. I would like to be able to type the quantity of sheets, and I would like for them to be automatically named in ascending order: "1", "2", "3", "4" etc.

I would really appreciate any help!!! Thank you so much!

 

[Smitty]

Welcome to the Board!

Here's something I use to create copies of an existing sheet. You should be able to adjust the loop to name the sheets instead of the Input Box. Note that there's no error handling in case a sheet already exists, but it can be added easily.

<font face=Calibri><SPAN style="color:#00007F">Public</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Public</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> CopySheetXTimes()<br>    <SPAN style="color:#00007F">Dim</SPAN> NewName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>        i = Application.InputBox("How many copies do you want?", "Number of Copies?", Type:=1)<br>        <br>        <SPAN style="color:#00007F">Set</SPAN> ws = ActiveSheet<br>        <br>        <SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> i<br>            ws.Copy After:=Sheets(Sheets.Count)<br>                    NewName = InputBox("Enter the new sheet name", "New Sheet Name")<br>            ActiveSheet.Name = NewName<br>        <SPAN style="color:#00007F">Next</SPAN> x<br>        <br>        ws.Activate<br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH.

 

[alelea91]

Welcome to the Board!

Here's something I use to create copies of an existing sheet. You should be able to adjust the loop to name the sheets instead of the Input Box. Note that there's no error handling in case a sheet already exists, but it can be added easily.

Public ws As Worksheet
Public i As Long

Sub CopySheetXTimes()
****Dim NewName As String
****Dim x As Long
****
********i = Application.InputBox("How many copies do you want?", "Number of Copies?", Type:=1)
********
********Set ws = ActiveSheet
********
********For x = 1 To i
************ws.Copy After:=Sheets(Sheets.Count)
********************NewName = InputBox("Enter the new sheet name", "New Sheet Name")
************ActiveSheet.Name = NewName
********Next x
********
********ws.Activate
********
End Sub

HTH.

That was so helpful. Thank you so much!!!