Replacing whitespace in worksheet names with an underscore

[Impos2004]

Hi,

I'm not very competent with VBA and am having trouble with a script that creates hyperlinks to other worksheets in the workbook.

The worksheet names are dictated externally and occasionally contain white spaces, which means that the hyperlinks that my VBA creates don't work for those tabs. I would like to write a line or two of code to replace all of the whitespaces in all of the worksheet names with underscores, which will solve the hyperlink issue.

Does anyone know how I can do this?

Many thanks for your time

David

 

[Joe4]

Welcome to the Board!

This bit of code should do what you want:

    Dim ws As Worksheet
      
    For Each ws In ActiveWorkbook.Sheets
        ws.Name = Replace(ws.Name, " ", "_")
    Next ws

 

[RoryA]

Why do you need to replace the spaces? If your code to add the hyperlinks encloses the sheet name in single quotes (e.g. use 'sheet with spaces' in the SubAddress) then the hyperlink should work.

 

[Impos2004]

Perfect, thank you!

The reason I can't use single quotes was because the worksheet names are variable, so it is looking them up itself. There could be dozens of them with different and unpredicable names.