Find and Replace text in formula based on sheet name

Hi everyone,
I've really never looked at macros or anything but I think I have an issue that could be solved with something along those lines.

I have a workbook with several different sheets in it. All of these sheets are identical layout but the values need to be different. Each cell references other workbooks and sheets in their respective workbooks. What I want to do is a find and replace that works like: "Check to see what the sheet name is, if sheet name is "Sheet B" then find the text "Sheet A" and replace it with "Sheet B" and i only want it to look in cells I5:O184.

I have one worksheet created with all of the references in each cell equaling a specific cell in another file. Each cell references different files but everything is named a certain way so theoretically I can copy my main sheet as many times as i need to and just change the name and then run some kind of macro/script and it will modify all of the links based on the sheet name.

I hope this makes sense. This stuff is hard to explain with out seeing my workbooks.

I'm not sure if i follow. I'm not familiar with the indirect() function so i'm not sure how to make it work, but when I finish writing this I will research that indirect(). I also don't feel like i described it very well. I'll see if i can elaborate. I will simplify what i am doing and only talk about a couple sheets and cells.

My main workbook as two sheets:

Sheet A --> Named: 1322 2 Stall
Sheet B --> Named: 1322 3 Stall (copied from Sheet A)

----------

In "1322 2 Stall" (Sheet A) my cell A1 references another workbook via this formula: ='[Framing Budget 9-18-14.xlsx]1322 2 Stall'!D4

Since I have just copied Sheet B from sheet A, cell A1 still equals the above formula.

----------

I need "1322 3 Stall" (Sheet B) cell A1 to reference this: ='[Framing Budget 9-18-14.xlsx]1322 3 Stall'!D4

----------

As a test I did a find and replace on sheet be for: Find = 1322 2 Stall - Replace = 1322 3 Stall - and set it to look in cell formulas and it worked. The issue is when I finish copying all of the sheets I need I will have a lot of finding and replacing to do. Which, if I have to I will do, but this will be something that requires ongoing maintenance so it would be nice if I can figure out an automated way to do this.

----------

My hope is that I can make one long macro or script or whatever you call it to do the following:

Look at the sheet name
If the name is 1322 3 stall, Find and replace all text in the formulas to say 1322 3 Stall (right now every formula in Sheet A references the words "1322 2 Stall" so when I copy that sheet to make sheets A - W it should stay the same, there for i can always look for that phrase.

Similarly if the sheet name is 1502 3 Stall, find (1322 2 Stall) and replace all formulas to say 1502 3 Stall.

Does this clarify?

Thanks etaf,
I haven't had a chance to try it yet but from what I can see it will work perfectly. I do have one question though. I know it sounds really silly, but i have been working on this project for 8 months and I'm almost to the point that if i were able to do the find and replace as described, it would be entirely possible to run the macro and literally watch everything I've worked 60 hours a week on for 8 months to come together at once. This would be indescribably satisfying. I realize that this is sort of pointless in the grand scheme of things, but there will probably never be another time in my life that I can click a button and watch almost 2000 hours worth of work happen in front of my eyes.

I like your way because I think it will function much better going forward, but I have another request. I dont need to pull the sheet name using this formula =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

I could just, in theory, copy all the sheets, name them what i want and run a macro that says something like "on sheet "1322 2 Stall" enter "1322 2 Stall" in cell Z1" "On 1322 3 Stall enter "1322 3 Stall" in cell Z1" and so on. Right?

Then after I get all the sheets copied and everything set up I could just run the macro and it would change the text in cell Z1 and everything would auto fill?

It doesn't work unless i have the workbook "framing budget 9-18-14.xlsx" open.

If i open the sheet it updates...stays that way when I close it, but that means i have to open all 50 or so sheets i have, enter this new formula in and then close it. It appears as though I'd have to open all the sheets each time I open the main sheet that references all these other ones.

Does this make sense to you?

I have been playing with your post from earlier and I cant seem to get it to work. I tried both pulling the name of the sheet using this

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

and also just typing the name I wanted to replace into a cell

I used cell A1 and typed it "1322 3 Stall" which is the name of the sheet I want to reference but it didn't work it gives me a #REF error.

Here is the exact formula i entered

=INDIRECT("'Z:\Project Estimating\Project Estimating 2014\Sub-Contractors\Framing\[Framing Budget 9-18-14.xlsx]"&A1&"'!G4")

Did I miss something?

well I honestly think I figured out a way to create what I want. Probably way more time spent than I should have but oh well. What I ended up doing what recording a macro to do what i wanted...it got me close. I then spent a few hours researching the rest of it to fill in the blanks. What i didn't know when i started is that the macro recorded the way it did. When I tried before my post I must have messed up and so I didn't think I would do a find and replace.

Here is the code.

Sub CreateBudget()
'
' CreateBudget
'

'Create the 1322 3 Stall Budget Sheet

    Sheets("1322 2 Stall").Select
    Sheets("1322 2 Stall").Copy After:=Sheets(1)
    Sheets("1322 2 Stall (2)").Select
    Sheets("1322 2 Stall (2)").Name = "1322 3 Stall"
    Sheets("1322 3 Stall").Select
    [A1].Value = "1322 3 Stall"
    
'Fill in the 1322 3 Stall Budget
    Sheets("1322 3 Stall").Select
    Cells.Replace What:="1322 2 Stall", Replacement:="1322 3 Stall", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

End Sub

I did a section like this for all the tabs i need to create. It seems to have worked great. I can't test it fully since i'm at home on VPN and it takes way to long to update a workbook, but I did a quick mock up and it seemed to work perfect.