Macro to repeat a Search and Replace on every worksheet

[nicolapellow]

A predecessor has incorrectly spelled a word and I don't know how often it occurs in the numerous spreadsheets so wanted to create a macro which would quickly search each sheet within a workbook, correcting any that it found. Have successfully used the 'for each wksht in worksheets' approach before for changing headers and footers, but it didn't seem to work this time.

Sub SearchandReplace()
'
' SearchandReplace Macro
' Search for a word and replace on each worksheet in the workbook

For Each Wksht In Worksheets

Cells.Replace What:="negitive", Replacement:="Negative", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Next Wksht

End Sub

 

[JoeMo]

Try changing this line:
Cells.Replace What:="negitive", Replacement:="Negative", .....
to this
Wksht.Cells.Replace What:="negitive", Replacement:="Negative", .....

 

[nicolapellow]

Thanks! That did the trick perfectly

Try changing this line:
Cells.Replace What:="negitive", Replacement:="Negative", .....
to this
Wksht.Cells.Replace What:="negitive", Replacement:="Negative", .....

 

[sdsurzh]

Hi,

Press control H and select option change to workbook under "within" (default will be sheet) now replace all.

Thanks,
SK

 

[JoeMo]

Thanks! That did the trick perfectly

You are welcome - thanks for the reply.