Macro code to find and replace text in different worksheets based on master worksheet data

[vigneshvenkat]

Worksheet Name| Find text|Replacing Text
Sheet2 | Table A |Table 1
Sheet2 | Table B | Table 2
Sheet3 | Table A | Table 3
Sheet3 | Table B | Table 4
Sheet4 | Table A | Table 5
Sheet4 | Table B | Table 6
I have a master worksheet - Sheet1 with the above data
My requirement is such that I need to find the texts for e.g 'Table A', 'Table B' in worksheet:Sheet2 and replace them with values 'Table 1' and 'Table 2' respectively.
Similarly I need find the texts for e.g Table A, Table B in worksheet:Sheet3 and replace them with values 'Table 3' and 'Table 4' and so on.
In real time, I may have 100+ worksheets similarly.
Basically I need to find 2 old values and replace them with 2 new values in every worksheet based on the data in the master worksheet .
Please help me out by sharing the Macro code as I am a newbie to Excel Macros.

 

[Martvg]

Hi,

I guess this is what you are looking for, you might have to play around a bit with the ranges and the sheet name.

Sub loopR()

    Dim x As Integer
    Dim y As Integer
    Dim OldR
    Dim NewR
    Dim Sht
    
    y = Sheets("Master").Range("A2000").End(xlUp).Row 'Defines y as being the row number of the last row in column A of the active sheet.
    
    For x = 2 To y                'Starts a loop from row x to the last row
                                  'x is assigned to a two because we want to start from the second row, as the first contains headers.
        
        Sht = Sheets("Master").Cells(x, 1).Value    'define the sheet name from column A in Master sheet
        OldR = Sheets("Master").Cells(x, 2).Value   'define the to be replaced value from Column B in Master sheet
        NewR = Sheets("Master").Cells(x, 3).Value   'define the new value from Column C in Master sheet
    
        Worksheets(Sht).Cells.Replace What:=OldR, Replacement:=NewR 'Replaces the values in the right worksheet
    
    Next


End Sub

Best regards,

 

[vigneshvenkat]

Thanks a lot Martvg. Works like a charm . You are my saviour

Hi,

I guess this is what you are looking for, you might have to play around a bit with the ranges and the sheet name.

Sub loopR()

    Dim x As Integer
    Dim y As Integer
    Dim OldR
    Dim NewR
    Dim Sht
    
    y = Sheets("Master").Range("A2000").End(xlUp).Row 'Defines y as being the row number of the last row in column A of the active sheet.
    
    For x = 2 To y                'Starts a loop from row x to the last row
                                  'x is assigned to a two because we want to start from the second row, as the first contains headers.
        
        Sht = Sheets("Master").Cells(x, 1).Value    'define the sheet name from column A in Master sheet
        OldR = Sheets("Master").Cells(x, 2).Value   'define the to be replaced value from Column B in Master sheet
        NewR = Sheets("Master").Cells(x, 3).Value   'define the new value from Column C in Master sheet
    
        Worksheets(Sht).Cells.Replace What:=OldR, Replacement:=NewR 'Replaces the values in the right worksheet
    
    Next


End Sub

Best regards,