Macro that isn't working

[Brad]

Should be really simple, but I'm missing something basic.
In the Cell.Replace (I want to replace the formula for the entire workbook)
&
If this can be simplified - I would appreciate it...


Sub MakeTheChange()
Dim inpStr1 As String
Dim inpStr2 As String
Dim inpStr3 As String
Dim inpStr4 As String
Dim inpStr5 As String
Dim inpStr6 As String

inpStr1 = "Intranet12|01|09"
inpStr2 = "Intranet11|10|09"
inpStr3 = "Intranet09|01|09"
inpStr4 = "Rates12|01|09"
inpStr5 = "Rates11|10|09"
inpStr6 = "Rates09|01|09"

Cells.Replace What:=inpStr2, Replacement:=inpStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Cells.Replace What:=inpStr3, Replacement:=inpStr2, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False

Sheets(inpStr5).Name = inpStr4
Sheets(inpStr6).Name = inpStr5

End Sub

 

[Jim Thomlinson]

The code you have only works onthe active sheet. This line
Cells.Replace ...
is executed as
Activesheet.Cells.Replace

You need to create worksheet object and travers through all of the sheets

Sub MakeTheChange()
Dim inpStr1 As String
Dim inpStr2 As String
Dim inpStr3 As String
Dim inpStr4 As String
Dim inpStr5 As String
Dim inpStr6 As String
dim wks as worsheet

inpStr1 = "Intranet12|01|09"
inpStr2 = "Intranet11|10|09"
inpStr3 = "Intranet09|01|09"
inpStr4 = "Rates12|01|09"
inpStr5 = "Rates11|10|09"
inpStr6 = "Rates09|01|09"

for each wks in worksheets
with wks
..Cells.Replace What:=inpStr2, Replacement:=inpStr1, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
..Cells.Replace What:=inpStr3, Replacement:=inpStr2, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
end with
next wks
Sheets(inpStr5).Name = inpStr4
Sheets(inpStr6).Name = inpStr5

End Sub

 

[Brad]

Thought that might be the case and I was sooo close to the right answer - was
missing the leading "." in front of the cell.replace

As always - Jim - thanks for the help....