Bulk update of formula ranges in name manager

[Tristo]

Hey All,
Was hoping for some help in regards to a bulk update of all the line items in the name manager. Currently the ranges under the "Refers to" all start from the column T and I need to change them to start from the column H.
Started updating them manually but quickly realized that I would be here all day if trying to do all 3,500 of them. Figured there was a quick macro I could run that looks for the letter T and replaces with the letter H but I'm rubbish when it comes to setting them up so any help would be appreciated.
Thanks.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Tristo()
   Dim Nm As Name
   
   For Each Nm In ThisWorkbook.Names
      If InStr(Nm.RefersTo, "$T$") Then Nm.RefersTo = Replace(Nm.RefersTo, "$T$", "$H$")
   Next Nm
End Sub

 

[Tristo]

Hi & welcome to MrExcel.
How about

Sub Tristo()
   Dim Nm As Name
  
   For Each Nm In ThisWorkbook.Names
      If InStr(Nm.RefersTo, "$T$") Then Nm.RefersTo = Replace(Nm.RefersTo, "$T$", "$H$")
   Next Nm
End Sub

Hey Fluff,
That worked a treat. Thanks for your help, much appreciated!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Samuel_96]

Hi

I know this is an old thread but wondered if someone might be able to help.

Total novice here. The above VBA works really well but I wondered if there was a way in which I could insert something into the formula rather than removing it. For example, currently my name manager refers to =Sheet1!$J$1 but I want to bulk change this to =Files(Sheet1!$J$1). I would do it manually but I have about 5000 cells. If someone can help that would help me massively.

Thanks
Sam

 

[Fluff]

As this is significantly different from the original question, please start a thread of your own. Thanks

 

[Samuel_96]

Sorry. I thought it was a similar kind of thing. Apologies.

 

[Fluff]

Not a problem.

 

[cricket1001]

I wish that when you told Samuel_96 that his 2nd question needs to be in a new thread, that there could be a link to that thread. I would love to know the answer to his 2nd question.

 

[Fluff]

Samuel_96 never started a new thread so there is nothing to link to.