Change text in a macro with another macro

[jjpkraft]

I have a excel template that won't open unless the file "Commercial Menu.xls" is open.

On Error GoTo MenuNotOpenError 
Workbooks("Commercial Menu.xls").Activate 
Workbooks(ChecklistWorkbookName).Activate 

MenuNotOpenError: 
    Select Case Err 
        Case 9 
            MsgBox prompt:="Commercial UW Menu must be open." 
        Case Else 
        End Select 
    Workbooks(ChecklistWorkbookName).Activate 
    ActiveWorkbook.Close SaveChanges:=False 
    Exit Sub

The problem is I had to change the file that needs to be open from "Commercial Menu.xls" to "UW Menu.xls". I've corrected the code so new files using the template open as long as "UW Menu.xls" is open but old files won't open since "Commercial Menu.xls" no longer exists.

Is there a way to open all the workbooks in a folder (one at a time) and replace all occurences of "Commercial" with "UW" in the code?

This one has me stumped.

Jason

 

[jjpkraft]

Works great. Thanks for all your help.

 

[sbendbuckeye]

You are welcome, glad I could help. Another good site that I frequent is www.tek-tips.com.