SaveAs VBA Removing Special Characters

[tcorcoran15]

Hello,

I have the following code which names a sheet after a specified cell and then copies into a new workbook.

The next stage attempts to save the workbook after another cell value however this is where I became stuck.

Cell D2 can contain special characters so the save as isn't working correctly.

Is it possible to save as a specified cell removing the special characters? Without removing the special characters from the cell?

For example, ABC LTD T/A TOMS CAKE SHOP would become ABC LTD TA TOMS CAKE SHOP

Any help is greatly appreciated.

Set wh = Worksheets(ActiveSheet.Name)
        ActiveSheet.Name = wh.Range("Q2").Value
        
        ActiveSheet.Copy
        
        Application.DisplayAlerts = False    ' suppress overwrite warning message
        ActiveWorkbook.SaveAs Filename:="X:\Company Name\Product\Product - Live\Reports\Credit Control Files\" & Range("D2"), _
                          FileFormat:=51, _
                          CreateBackup:=False
                          
        Application.DisplayAlerts = True
        ActiveWorkbook.Close SaveChanges:=False

 

[Trevor G]

This link show you how to right a function that you could use in an empty cell then refer to that cell for the name of the workbook then you could delete the cell content. https://www.extendoffice.com/documents/excel/3483-excel-remove-special-characters.html

Function RemoveSpecial(Str As String) As String
'updatebyExtendoffice 20160303
Dim xChars As String
Dim I As Long
xChars = "/"
For I = 1 To Len(xChars)
Str = Replace$(Str, Mid$(xChars, I, 1), "")
Next
RemoveSpecial = Str
End Function

 

[tcorcoran15]

Thanks for your help Trevor.

I used the above function, although I wanted to stay away from using empty cells so tried the following and found it worked perfectly.

Set wh = Worksheets(ActiveSheet.Name)
        ActiveSheet.Name = wh.Range("Q2").Value

        ActiveSheet.Copy

        Application.DisplayAlerts = False    ' suppress overwrite warning message
        ActiveWorkbook.SaveAs Filename:="X:\Company Name\Product\Product - Live\Reports\Credit Control Files\" & RemovePunctuation(Range("D2")), _
                          FileFormat:=51, _
                          CreateBackup:=False

        Application.DisplayAlerts = True
        ActiveWorkbook.Close SaveChanges:=False

 

[Trevor G]

Pleased to read you have a working solution. Thank you for letting me know.