Auto Find/Replace + Marco sort?

Two quick ones. People seem to be calling shovels spades etc if different sheets, so what I'm looking for is something that searches all worksheets in the work book for occurances of A1 (shovel) and replace it with A2 (spades). The list will be about 100 cells long and the idea would be that I could copy a sheet called "replace" with this list in, run it and standardise all the terms in the list within that workbook. Does it sound feasable?

The other query I'm not sure can be done. When I've tidied all these 'names' to a standard one, I want to sort the data (sometimes rows, sometimes columns) alphabeticalled for instance by the A1 to A10, or by A1 to Z1. Simply using the sort option loses all the references as is doesnt change any cells linked to that cell. The only way I could think to make keep the other cells pointing to where they should is to cut and paste/each row/column in alphabetical order manually, but it's taking me forever! Is there another way?

Regards,

Mike

Originally Posted by Simon Lloyd

Mikeyfear, the code below goes in a standard module (Alt+F11, then right click in the window on the left where you see ThisWorkbook then choose Insert, Module) paste the code in its entirity.

Sub Find_n_Replace()
Dim Rng As Range, MyCell As Range
Dim Sht As Worksheet
Set Rng = Sheets("Sheet3").Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
' change sheet name to suit as this is where your list will be
For Each Sht In Sheets
Application.ScreenUpdating = False
Sht.Select
If Sht.Name = "Sheet3" Then GoTo Nxt' change sheet like above
For Each MyCell In Rng
     Cells.Replace What:=MyCell.Value, Replacement:=MyCell.Offset(0, 1).Value, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Next MyCell
Nxt:
Next Sht
Application.ScreenUpdating = True
End Sub

My sheet 3 will be the sheet you have list on, the way it works is in column A have the list of words to look for and in column B have the words you want to change them for, so in A1 if you had Spade then in B1 you must have Shovel, when you run the code above it will then change Spade to Shovel, you can of course add a button to your worksheet or toolbar and assign this macro or from the window where you pasted the code click the run button or from excels toolbar choose Tools, Macro, Macro's and then run the one you want, it is in this option window that you can set a keyboard shortcut to run the macro.

That's cracking, thanks a million!

If I want it to highlight the cells it replaces what do I need to add?

Is the sorting marco I was talking about in the original post viable too or is that a non starter?

Regards,

Mike