hyperlinks and sheet names with spaces

Kev · #4 01-30-2017, 08:45 AM

Another way to fix the problem would be to rename worksheets that contain non-valid characters
The VBA is currently set to display what happens via a message box - apostrophe is replaced with ~ and space replaced with _
To rename the worksheets remove the apostrophe at the beginning of this line

Code:

'ws.Name = newName

Code:

Sub remove_Spaces_and_Apostrophes()
    Dim oldName As String, newName As String, ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
    oldName = ws.Name
    newName = Replace(ws.Name, "'", "~")
    newName = Replace(newName, " ", "_")
    'build up message string
    msg = oldName & "   becomes   " & newName & vbCr & msg
    'replace the name
    'ws.Name = newName
Next

    MsgBox "Old Name   with     New Name" & vbCr & vbCr & msg

End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Open an existing sheet by clicking on a cell in a master sheet	darbybrown	Excel	3	09-12-2016 05:12 PM
Populate sheet 3 with data from sheet 1 and sheet 2	speck	Excel Programming	0	01-14-2015 07:54 AM
Adding names to sheet for printing	WV8VFD	Excel	4	12-01-2013 11:02 PM
How to enter names in Resource Pool/names	pstein	Project	1	03-26-2012 07:37 AM
Word to Excel hyperlinks and spaces	gak	Word	1	09-14-2008 08:38 AM