Adding & Naming Sheets "X" No of Sheets Dynamically

Dear Forum,

I need to Add & Rename Sheets a particular no of Sheets Dynamically using a Command Button..

I have an Index Sheet where I would be typing the name of the Sheet and a Command button to execute the operation of Adding the Sheet with the name mentioned in the Column C..

Please find the attachment below..I owuld appreciate if someone adds a new attachemnt as Im not good in VBA...

Please see attachment. I made the range C2:C22, but you can make it any range you want. I imagine you wouldn't want more than 20 sheets, but you never know.

Sub AddAsLastWorksheet()

    Dim Cells As Range

    For Each Cells In Range("C2:C22")
        If Cells = "" Then
            Sheets("Main Sheet").Activate
            Exit Sub

        Else
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Worksheets("Main Sheet").Range(Cells.Address).Value

        End If

    Next Cells

End Sub

Hi all,

I recommend changing the name of the variable which is dimmed as "Cells" because this word has an existing meaning* in Excel's VBA & you may confuse Excel &/or yourself by using this approach. A common technique to prevent this confusion is to remove the vowels eg

 dim Cll as range

.

*(ie as an Excel Keyword "Cells" means all cells on the defined sheet or the active sheet if the object container (eg "worksheets("sheet1").cells") is not specified))

hth
Rob

Ok, here are some examples for you. The attached sheet does much more than you asked. I liked the idea of dynamically controlling sheets from one master sheet, so I played around with it. The attached sheet will allow you to add and/or rename a specified sheet, like you asked, but you can also have it run down the list and add and/or rename as needed. Also, the button on the Main Sheet reflects the sheet # being added/renamed.

I ran into some trouble when I was deleting and adding sheets, so I wrote some code that goes through and re-syncs the sheetnames with the codenames.

So, anyway, take what you can from the attached.

Below is the bare-basics of what you asked for. If there is a sheet already named what is given, it will cause an error. Also, it will not rename any sheets.

Sub AddAsLastWorksheet()

    Dim SheetRow As Integer
    Dim SheetsAddRename As String

    SheetRow = Sheets("Main Sheet").Range("F1").Value
 
   If Sheets("Main Sheet").Cells(SheetRow, 3).Value = "" Then
        Sheets("Main Sheet").Activate
        Exit Sub

    Else
            
         Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Sheets("Main Sheet").Cells(SheetRow, 3).Value
                  
    End If

End Sub

*** Edit ****

Oh yeah, the attached workbook assumes that you have Tools->Options->Security->Macro Security->Trusted Publishers->"Trus access to Visual Basic Project" checkbox checked. If it is not checked, the re-syncing of sheet.codenames and sheet.names will cause an error when Main Sheet is activated.

Thanks a lot Whizbang, you actually over-exceeded my expectations..I liked the idea of having two command-butttons for Adding each sheet or at one-go...

But the problem arises when I tried to delete the Sheets and due to my weakness in VBA cant really repair that...

Can the Sheets be deleted via a button...This is just bcos of your splendid efforts...that I though of getting it done with a command button or maybe having a cheque-box or Dro-down having an option of delete in column D next ...to each Sheet Name in the Main SHeet..

What Say , is it possible?

You are right I had not flushed the values and was getting the Regukar END DEBUG error, sorry but since I am no good in VBA at all not able to even check or handle the errors or even explain them..

But honestly very satisfied with your efforts as this gives complete control to the user from the Main Sheet...

CAn something also be worked out whereby we reduce the no of command buttons , but do not know how, Maybe some toggle-switches I dont know how but since you are good in VBA maybe you can come up with something...

Can we delete the Name and the Sheet at the same time to surpass the error, unfortunately Im not in a position to gauge the difficulties in VBA but just a suggestion may be by clearing contents of the selected Sheet Name and by Deleting the Sheet at the same time..

Sequential Numbering is just for logical purpose and no other reason..CAn the Sheet Numbers be made to be equal to the values in Column A i.e "Sheet "& A2

e4excel,

Please refrain from quoting whole posts. It's just clutter.

Thanks.

Ok. Got all the bugs I could find worked out. I don't have any notes in the code to help you follow it. If you want, I'll take the time at some point this week to add notes. But I figured you'd want a working copy to look at and learn from in the mean time.

What this file does:
*Add a sheet or sheets (one at a time or all at once)
*Rename existing sheets dynamically (one at a time or all at once)
*Delete existing sheets (one at a time or all at once)
*Automatically adjusts sheet.Codenames to be sequencial.

How to run:
To add a new sheet: In column C, go to first blank line and enter a name for the new sheet. In cell G1, enter the row number for the new sheet (or type "All") and click the button
To rename a sheet:In column C, go to desired row and edit the name. In cell G1, enter the row number for the sheet to be renamed (or type "All") and click the button
To delete a sheet: Enter an x (Or anything but a null value) into column D of the desired row. In cell G1, enter the row number for the sheet being deleted (or type "All") and click the button

Thanks to Andy Pope for giving me the bit of code that I needed.

See this thread: http://www.excelforum.com/excel-prog...-not-open.html

See the attached for a working example.