How to replace several old values with new values on multiple tabs?

[Nuutti]

I have a bit more than hundred tabs and want to clean the data on those tabs. Some values are written in multiple ways. If I do not want to find & replace all old values one by one on all tabs, how can I find old value and replace them with new values at once, if I create a new tab with old values in A column and new values in B column?

Table on new tab with old and new values could look like this:

Old New
abc Abc Inc
EFG PLC Efg Plc
AAA Fund AAA Fund Company
AAA AAA Fund Company

 

[My Aswer Is This]

Where on the old tabs should we look for the values?
Do we need to search the entire sheet or can we just look in column "A"

And will the value in the cell be only "abc" or may the cell have "we like the abc company".

 

[Nuutti]

The tabs have the old value to be replaced either in a or b columns. And the old value equals exact match of whole cell not only parts of it.

 

[My Aswer Is This]

Assuming all your old values are in Column ("A") and your new Values are in column ("B") on Sheet (1)
And sheet (1) is the sheet in the far left position on you Workbook Tab bar.

Run this script:

Sub Test_Me()
Application.ScreenUpdating = False
Dim i As Long
Dim c As Long
Dim b As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Sheets(1).Activate
Lastrowa = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
    
    For c = 2 To Sheets.Count
        Lastrow = Sheets(c).Cells(Rows.Count, "A").End(xlUp).Row

        For i = 1 To Lastrow

            For b = 1 To Lastrowa
                If Sheets(c).Range("A" & i).Value = Sheets(1).Range("A" & b).Value Or Sheets(c).Range("B" & i).Value = Sheets(1).Range("A" & b).Value Then
                    Sheets(c).Range("A" & i).Value = Sheets(1).Range("B" & b).Value
                End If
            Next
        Next
    Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

After posting the script above I had thought about column A and B

When I first thought about working on this script I assumed we would only be looking for these old values in Column A but then when you said it may be in column A or B things got more complicated.

If the value was found in column A but not B should both column A and B be changed to the new value and what if it was found in both A and B.

You can try the script I provided and if it does not work for you maybe someone else here at Mr. Excel can help out here.

I have a hard time when we get into too many if and or statements.

And I do not understand why you would have a company name in both column A and B sometimes but not all times or why it would ever be in more then one field.

 

[tonyyy]

Another approach, similar to @MAIT's but uses the Replace function...

Sub ReplaceOld()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim Lastrow As Long, i As Long
Dim r As Range, rng As Range

Set ws = Sheets(1)
Lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = ws.Range("A2:A" & Lastrow)

For i = 2 To Sheets.Count
    For Each r In rng
        Sheets(i).Columns("A:B").Replace _
            What:=r.Value, Replacement:=r.Offset(0, 1).Value, _
            SearchOrder:=xlByColumns, MatchCase:=True
    Next
Next

Application.ScreenUpdating = True
MsgBox "Done!"
End Sub

Cheers,

tonyyy

 

[Nuutti]

Thanks guys. I used tonyyy´s script. It worked.

However it is also replacing the original "old value" with "new value" on sheet (1) in far left position on my Workbook Tabs. What kind of tiny update I have to do to the script to keep values untouched on that sheet 1 that is summary tab of all old and new values?

 

[tonyyy]

Nuutti,

The replacements start on sheets(2) and continue until the last sheet. If replacements are occurring on what appears to be the first sheet, then maybe it's not really the first sheet. Are you scrolled completely to the left on the tabs bar? Is there perhaps a hidden sheet?