Macro to change text in macro on control button in workbook?

[Rookeby7]

I have inherited a process which has a template workbook with Macro buttons so users can move to specific sheets.

The template has been used to create approx 700 files.

I have been making some amendments to the template, which has included changing one of the worksheet names - this means one of the macro buttons no longer works as it is looking for a sheet which no longer exists.

Is it possible to create a macro to open an embedded macro and find/repalce the sheet name? (and loop through 700 files?)

Or is it possible to assign a new macro to the existing button? (and loop through 700 files?)

Hope someone can help, thanks in advance

 

[Yongle]

How about amending the macro so that it is looking for the correct sheet ?

 

[footoo]

If the sheet codename is the same for all files, just change your macro to refer to the codename instead of the sheet tab name.

 

[Rookeby7]

edited

 

[Rookeby7]

edited

 

[Rookeby7]

How about amending the macro so that it is looking for the correct sheet ?

Yes I have done for my "new template" but I am looking for the best way to run the modifications in to all the existing workbooks.

One of the changes is the change of sheet name, so I'm looking for most efficient way to reflect the change of sheet name and update the macro assigned to the button so the macro button works takes user to newly named sheet.

I am new to the world of Macros and the idea of coding a macro to edit another macro is baffling me, but I suspect it is possible...

 

[Rookeby7]

If the sheet codename is the same for all files, just change your macro to refer to the codename instead of the sheet tab name.

thanks I will have a look at this

but can you suggest what would be the best way to make this change to the same macro in each of the 700 individual files?

 

[Yongle]

Would it not be simpler to change the sheet name back to the original name ?

 

[Rookeby7]

Would it not be simpler to change the sheet name back to the original name ?

Yes, but unfortunately the sheet name is causing confusion around the business... hence the requirement to change it

 

[footoo]

thanks I will have a look at this

but can you suggest what would be the best way to make this change to the same macro in each of the 700 individual files?

If the tab names have not been changed in the 700 files, why do you need to change the macros in those files?