Forum Discussion
Macro for adding named sheets in workbook from list
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
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
Ok, 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.
Got it, Riny_van_Eekelen. I had the developer mode button toggled on... I think that's why it wouldn't show button as active so it would run instead of just select the button. 🙂
I'm having problems duplicating the template; it references another sheet using a vlookup to supply descriptions and I keep getting these errors. Ah well, I can hit OK through out the process and it will create the tabs.
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!
