How to remove $ from sheet name?

  • Thread starter Thread starter Shalin
  • Start date Start date
S

Shalin

Hi,


How can we remove the '$' after the sheet name?? I don't
wanna program the name like parse it. Just simply rename
it to a name without '$'.
 
You want to get rid of all the $ signs:

Option Explicit
Sub testme01()
Dim wks As Worksheet

For Each wks In Worksheets
On Error Resume Next
wks.Name = Application.Substitute(wks.Name, "$", "")
If Err.Number <> 0 Then
MsgBox "cannot rename: " & wks.Name
Err.Clear
End If
On Error GoTo 0
Next wks

End Sub

Just the trailing $'s:

Option Explicit
Sub testme01B()
Dim wks As Worksheet

For Each wks In Worksheets
If Right(wks.Name, 1) = "$" Then
On Error Resume Next
wks.Name = Left(wks.Name, Len(wks.Name) - 1)
If Err.Number <> 0 Then
MsgBox "cannot rename: " & wks.Name
Err.Clear
End If
On Error GoTo 0
End If
Next wks

End Sub
 
Thanks for your support. But when I add this and set the
macro security level to low still this does not run Giving
macro security error. What should I do now??
 
First guess:

Change your macro security level to medium (and answer yes to the enable macros
prompt), then open the workbook with the macro.

The change to the security level isn't "retroactive" to the already opened
workbooks.
 
How are you trying to run the macro?

Do you use Tools|macro|macros... and select it and then click Run?

Try going into the VBE and clicking anywhere in your that procedure's code.
Then hit the F8 key to step through it.

What happens?
 
I run themacro in exactly the same way. It runs but as I
see step-by-step the sheet name it takes is Sheet1 and not
Sheet1$. But when I use this sheetname for my project it
takes Sheet1$. My project uses Apache server to extract
the name of the sheet from the ODBC source.

Thankx,

Shalin
 
I've never used ODBC, but I think that dollar sign isn't actually part of the
sheet name. It's the way the end of the name is indicated.

Just use something like this to chop that last character.

mysheetname = left(mySheetname,len(mysheetname)-1)
 
Back
Top