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:
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