By doing this you will see a pop-up menu like shown below; Upon clicking "More sheets..." you will see all Worksheets listed in a nice scrollable List Box as shown below Worksheet Index If this is still not what you need we can create a Worksheet Index. Name = "Index" End With For Each w Sheet In Worksheets If w Sheet.
First add a new sheet to the Workbook and call it "Index" (optional).
Sub Add Worksheets From Selection() Dim Cur Sheet As Worksheet Dim Source As Range Dim c As Range Set Cur Sheet = Active Sheet Set Source = Selection. Screen Updating = False For Each c In Source s Name = Trim(c.
Text) If Len(s Name) The macro essentially grabs each cell in your selection, creates a new worksheet, and then renames that worksheet according to whatever was in the cell.
The list updates when a new sheet is added, but does not update when a sheet name is changed or when a sheet is deleted.
This can be over come in a few ways, with the best being keeping the number of Worksheets in an Excel Workbook to a minimum. Range("A1"), Address:="", _ Sub Address:="Index", Text To Display:="Back to Index" End With Me. Now, each time you activate the Index sheet the old list will be cleared and a new one will be added. For instance, what if your list contains duplicates? These (and any number of other errors) could be anticipated and the code changed to handle such situations. Calculate() method will not work for all functions, I tested it on a sheet with add-in array functions.The macro checks to make sure that a particular cell actually contains something (you can't rename a worksheet if there is no name in the cell), but it still isn't nearly as robust as it might be.There could be other flaws in your list of worksheet names that might lead to errors when the macro is run.