VBA to replace text string containing "&" in formulas

I have following code in one of the macros.
The purpose is to replace oldname with newname in formulas a range of cells.
These names refer to worksheets in the excel workbook.
The replacement is being done based on user selection via dropdown.
So the summary sheet pulls data from selected worksheet.

It works fine for all names except when a worksheet name contains "&".
For example when a name is "B & G Annex", this code doesn't work.

HTML Code: 

Can someone suggest a workaround [besides renaming the worksheet as B and G Annex]?

Thanks in advance for help.

Can you upload sample?

Code should work fine. Tested and did replace reference to 'Test'!A1 to 'B & G Annex'!A1 without issue.

Originally Posted by CK76

Can you upload sample?

Code should work fine. Tested and did replace reference to 'Test'!A1 to 'B & G Annex'!A1 without issue.

It does work if the newname is B & G Annex. But then it won't work next time when oldname has "&" in it.

Hello modytrane,

Why don't you just change such names in the DropDown itself?

Regards.

I think the issue with not with "&" but space in old name.

Try something like...

oldname = IIf(InStr(Worksheets("test").Range("A1").Value, " "), "'" & Worksheets("test").Range("A1").Value & "'", Worksheets("test").Range("A1").Value)

Originally Posted by CK76

I think the issue with not with "&" but space in old name.

Try something like...

oldname = IIf(InStr(Worksheets("test").Range("A1").Value, " "), "'" & Worksheets("test").Range("A1").Value & "'", Worksheets("test").Range("A1").Value)

I realized the issue with space earlier so I have replaced space with "_" so now the name reads B_&_G_Annex.
It still doesn't work when oldname has "&" in it. If I replace "&" with "and" it works fine.

I'm unable to replicate your issue. Can you upload sample workbook where you are encountering issue?

In the attached file cell I9 has a drop down list.
One name has "&" in it.
Switching between all other names will change references in formulas below to correct name.
Once you pick the "B_&_G_Annex",the references will change but after that picking any other name it won't change the formulas.

Hope there is an easy fix.

Thanks again for you help.

That's because you need to add single quote when space or & is found in the sheet name.

So...

Sub track()
With Worksheets("Svc Report")
    oldname = IIf(InStr(.Range("AK1").Value, "&"), "'" & .Range("AK1").Value & "'", .Range("AK1").Value)
End With
newname = Worksheets("Svc Report").Range("I9").Value
Range("E19:N44").Select
    Selection.Replace What:=oldname, Replacement:="'" & newname & "'", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
    Range("C19").Select
Range("N15").Select
    Selection.Replace What:=oldname, Replacement:="'" & newname & "'", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
    Range("C19").Select
With ActiveSheet
.Range("AK1").Value = .Range("I9").Value
End With

End Sub

Thank you so much.
I should've known. I was adding single quote to newname but didn't do it for oldname.

I appreciate your help.

You are welcome, and thanks for the rep