My Sheet Name is Eg: PMCC 1 and I have Content in sheet is PMCC#01.
I would like to Convert Sheet Name into PMCC#01 with replace function.
I tried this code
temp = ws.Name
newtemp = Replace(temp,"","#0")
As result, I only get 1.
Add a comment
|
4 Answers
Won't you just need:
ws.Name = Replace(ws.Name, " ", "#0")
Main difference is " " instead of ""
Comments
Try:
Option Explicit
Sub test()
Dim strName As String
Dim ws As Worksheet
With ThisWorkbook
For Each ws In .Worksheets '<- Loop all sheets
With ws
strName = .Range("A1") '<- Let us assume that the new name appears in all sheets at range A1
.Name = strName '<- Change sheet name
End With
Next ws
End With
End Sub
Comments
The main issue is: you have created a variable, you have entered a value in it, coming from the sheet name, and you believe that modifying the value of that variable will automatically modify your sheet name.
This is not the way variables work: you specifically need to put your variable's value into the sheet name, something like:
temp = ws.Name
newtemp = Replacement_function(temp, ...) // not sure to use `Replace()` or `Substitute()`
ws.Name = newtemp
Comments
In case the numeric element of your worksheet name can be 10 or higher, you might want to do this:
temp = Split(ws_Name, " ")
newtemp = temp(0) & "#" & Format(temp(1), "00")
That way, PMCC 1 becomes PMCC#01 but PMCC 13 would become PMCC#13, not PMCC#013
