CODEWITHSUNDEEP
excelvba
Fameez
Fameez

Reputation: 1

Replace string with new value

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.

Upvotes: 0

Views: 51

Answers (4)

CLR
CLR

Reputation: 12254

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

Upvotes: 0

Dominique
Dominique

Reputation: 17493

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

Upvotes: 0

JvdV
JvdV

Reputation: 75840

Won't you just need:

ws.Name = Replace(ws.Name, " ", "#0")

Main difference is " " instead of ""

Upvotes: 1

Error 1004
Error 1004

Reputation: 8220

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

Upvotes: 0

Related Questions