Remove Dashline from multiple sheets name using macro

[Arsil Hadjar]

Dear Sirs,

May I have your help on the following
I received a workbook with multiple sheets
I want to make an indexsheets with hyperlink to each sheets
The problem​

• the sheetname contain dashline which make the hyperlink not work.
• (Each sheets have different name with dashline in the sheetname : abc 123 , bcd 345 , etc)
• I want the sheetname become abc123 , bcd345 etc.(sheetname without dashline in it)

1. How to remove dashline on every worksheetsname by using macro and make indexsheets with hyperlink to each sheets in workbooks by using macro

Looking forward to receiving your support in this regards

Best regards
Arsil Hadjar


​

 

[FormR]

the sheetname contain dashline which make the hyperlink not work.

Hi, if you want to post the code that creates the hyperlinks then we can probably amend that to handle the special characters without altering the sheet names as it's probably just a case of enclosing the sheet name with single quotes when creating the SubAddress string.

 

[Arsil Hadjar]

Hi, if you want to post the code that creates the hyperlinks then we can probably amend that to handle the special characters without altering the sheet names as it's probably just a case of enclosing the sheet name with single quotes when creating the SubAddress string.

Dear MrExcel MVP
Kindly please find the code for creating the hyperlinks that I used befor

Sub CreateIndexSheet()
Dim wSheet As Worksheet
ActiveWorkbook.Sheets.Add(Before:=Worksheets(1)).Name = "Contents" 'Call whatever you like
Range("A1").Select
Application.ScreenUpdating = False 'Prevents seeing all the flashing as it updates the sheet
For Each wSheet In Worksheets
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=wSheet.Name & "!A1", TextToDisplay:=wSheet.Name
ActiveCell.Offset(1, 0).Select 'Moves down a row
Next
Range("A1").EntireColumn.AutoFit
Range("A1").EntireRow.Delete 'Remove content sheet from content list
Application.ScreenUpdating = True
End Sub


Looking forward to having your further support in this regards

Best Regards
Arsil Hadjar

 

[FormR]

Hi, try replacing the ActiveSheet.Hyperlinks.Add... line with this:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & wSheet.Name & "'!A1", TextToDisplay:=wSheet.Name

 

[Arsil Hadjar]

Hi, try replacing the ActiveSheet.Hyperlinks.Add... line with this:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & wSheet.Name & "'!A1", TextToDisplay:=wSheet.Name

Dear Mr ForumR
Many thanks for the suggestion.
I have tried the modified code and it perfectly works

Many thanks for the kind support
Best regards

Arsil Hadjar