copy and replace data using looping macro

Hi All, I have 300 rows of data in sheet 4.

In Column C I have "old names", in column A i have "new names".

What i would like to do is search for any matches of C2 (old names) in sheet 1, and replace them with new names (A2).

Then the process needs to repeat but for C3 & A3, C4 & A4 ...........etc all the way to 300.

This would save alot of manual input - i'm just not sure where to begin with the coding.

Seems to work perfectly. Thank you very much

Sorry to bring this to the top - I have noticed that the code will replace part of words.

So if i wanted to change cat to dog, and i had a word such as catalogue, it would replace the "cat" part of this too.

So I need to add code to say match entire cell contents = true (or something similar). Could anyone help with that

Sorry about that. Check to see if this corrects the problem. I added:

LookAt:=xlWhole, _

I think that will correct your problem.

Sub ReplaceOldNames()
    Dim rg1 As Range, nLastRow As Long, i As Long
    
    'Set Range to Whole Worksheet "Sheet1"
    Set rg1 = ActiveWorkbook.Worksheets("Sheet1").Range("A1").SpecialCells(xlCellTypeLastCell)
    Set rg1 = ActiveWorkbook.Worksheets("Sheet1").Range("A1", rg1)
    
    With ActiveWorkbook.Worksheets("Sheet4")
        
        nLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To nLastRow
            rg1.Replace What:=.Cells(i, "C"), _
                        Replacement:=.Cells(i, "A"), _
                        LookAt:=xlWhole, _
                        MatchCase:=True
        Next i
    End With
End Sub

While I think that will correct your problem. If not, please upload your workbook, or a mockup of your workbook.

Thats great thank you