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

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

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