Error handling when creating sheets based on list and hyperlink

[gv29]

Hi,

I am struggling to solve for two issues.
1) When I use the code below and a name form my list in the master sheet is >31 characters or uses : /\ ..., I get Template(x) as a name. Is there any way to handle the errors by truncating the names and getting rid of the unallowed characters?
2) I would also like to create an index in another column of the master sheet with the names and hyperlink of the newly created sheets.

The code I am using to generate the sheets is:

Sub CreateSheetsFromList()
    Application.ScreenUpdating = False
    Calculate
    Dim projectCell As Range, projectIDcol As Range
    'Dim ws As Worksheet
    
    Set projectIDcol = Sheets("Portfolio Roadmap").Range("D4")
    Set projectIDcol = Range(projectIDcol, projectIDcol.End(xlDown))
    
    For Each projectCell In projectIDcol
      On Error Resume Next
        If IsError(Worksheets(projectCell.Value)) Then
    Sheets("template").Select ' Select the template as the new worksheet to create
    Sheets("template").Copy After:=Sheets(Sheets.Count) 'Creates a new worksheet based on the template. Worksheet will be named template(1), (2) ect....
    Sheets(Sheets.Count).Name = projectCell.Value ' Renames the worksheets from the index of project.
    
    End If
     
Next projectCell
Application.ScreenUpdating = True
Sheets("Portfolio Roadmap").Select
Calculate
End Sub

Thank you very much for your help,

Kind regards,

Guillaume

[JBeaucaire]

1) Add this function to your module, it does what it says it does, you can see in the code where you list the character to remove:

Function removeSpecial(sInput As String) As String
Dim sSpecialChars As String, i As Long
    
    sSpecialChars = "\/:*?™""®<>|.&@#(_+`©~);-+=^$!,'"             'This is your list of characters to be removed
    For i = 1 To Len(sSpecialChars)
        sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "")    'this will remove spaces
    Next
    removeSpecial = sInput

End Function

2) Amend your code to use that function, as well as select the first 30 character, or 29, whatever you want:

    Sheets(Sheets.Count).Name = Left(removeSpecial(projectCell.Value), 30) ' Renames the worksheets from the index of project.

[gv29]

Thanks it works like a charm.

Can anyone help me with the second part:
2) I would also like to create an index in another column of the master sheet with the names and hyperlink of the newly created sheets.

Thanks a lot

[JBeaucaire]

CREATE A HYPERLINKED LIST OF SHEETS IN A WORKBOOK ON A FRONT SHEET
This macro creates a "table of contents" on a sheet at the beginning of the workbook. Each sheet listed is also an active Hyperlink so click the name will take you directly to the sheet. It demonstrates how to also collect some key pieces of data from each sheet to add to the LIST sheet to be more informative. It puts a link on all your original sheets in cell A1 back to this new LIST sheet and you have an extremely fast navigation system for large workbooks. There's a sample file for you to see this operate.

Create Hyperlinked List of Sheets

Once the hyperlinks are created for you, you could reorganize them on that front sheet in any visual way you wish to make them easy to see all at once.

[gv29]

Thanks. It solves partially my issue as what I wanted to achieve is having the hyperlink created in a column next to the projectCell column and only for the newly created tabs.

[JBeaucaire]

The macro demonstrates all the techniques for creating a hyperlink and putting it in a cell. When you adapt those techniques into your specific goal/target then you will have knowingly absorbed this content and made it your own.

[gv29]

Thanks, I manage to sort it out thanks to the concept