Forum Discussion
Macro for adding named sheets in workbook from list
I am looking for a macro to add named sheets from a selected list to a workbook.
Specifically, each sheet would have the same content duplicated from a template sheet, but named differently according to a list on another sheet.
I believe your question is to add worksheets (copied from a template) into a workbook based on a list, rather than list all the worksheets in the workbook.
The attached file has a working example, which includes the following short and easy to adapt (to your own situation) piece of code:
Sub AddSheets() Dim SheetList As Object Dim i As Integer Dim SheetName As String Set SheetList = ThisWorkbook.Sheets("SheetsToAdd").Range("A1").CurrentRegion For i = 1 To SheetList.Rows.Count SheetName = SheetList.Cells(i, 1) Sheets("Template").Select Sheets("Template").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = SheetName Next i End Sub
9 Replies
I believe your question is to add worksheets (copied from a template) into a workbook based on a list, rather than list all the worksheets in the workbook.
The attached file has a working example, which includes the following short and easy to adapt (to your own situation) piece of code:
Sub AddSheets() Dim SheetList As Object Dim i As Integer Dim SheetName As String Set SheetList = ThisWorkbook.Sheets("SheetsToAdd").Range("A1").CurrentRegion For i = 1 To SheetList.Rows.Count SheetName = SheetList.Cells(i, 1) Sheets("Template").Select Sheets("Template").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = SheetName Next i End SubOk, Riny_van_Eekelen, now you've sent my squirrels on a rabbit trail trying to figure out the control... would you be willing to share how you created the "Add Sheets" button?
Sure! Do you have the "Developer" ribbon on your Excel? If not, you can find how to do that here:
https://support.office.com/en-us/article/Show-the-Developer-tab-E1192344-5E56-4D45-931B-E5FD9BEA2D45
I'm on a Mac and for me it looks like this.
I used the "Button" and then clicked in sheet where I wanted to show it. Right-click on the button to assign a macro.
You can also change the text in the button (click inside the button), move it (hover over the button until you see 4 small arrows pointing in all directions) or resize it (drag the white squares). It's all quite intuitive. Read in the help screens and dare to experiment a little. Just save your work before you do anything. Should you not be able to figure it out, let me know.
Riny_van_Eekelen Thank you for your response, this is exactly what I was looking for! I knew it would be simple, but didn't know exactly how to write the script. This will make it sooooo easy to create new spreadsheets for each date needed!
- Hello,
Kindly find the solution to your question in the link below
https://www.automateexcel.com/vba/list-all-sheets-in-workbook/
If that answers your question, kindly accept as the Best Response. ThanksAbiola1 Thank you for your response. My request was not as clear as it should have been. Riny_van_Eekelen was able to understand what I was looking for.
Thanks again for trying.
