Excel Macro to Find and Replace in multiple sheet

Below are the two Sub-procedure which can be used to find and replace particular search term with replace value. So this macro will run on multiple sheet in same directory. In below code you have to make following changes according to your settings.

Pathname - I have mentioned the directory where all my sheet is present. This macro will run for all the sheet present in the directory you mention.

Filename  - If you notice I have give as "*csv". So I was using macro for CSV files in the particular directory. Change  the extension accordingly ex:xlsx or xls.

Search and Replacement - Use this to specify the search term and replace term.

Columns(3) - Here I am running this search and replace only for column C in the sheet.

Worksheets(1) - This means I am running the macro only in first sheet in the workbook.

How to run:

To run this macro open a blank Excel Sheet and press 'ALT + F11'.

It will open Visual Basic editor. And right click on sheet -> Insert -> Module. Then copy the below code in white page.

Sub ProcessFiles()
    Dim Filename, Pathname As String
    Dim wb As Workbook

    Pathname = "D:\Fvr\convibo\data1 - Copy\"
    Filename = Dir(Pathname & "*.csv")
    Do While Filename <> ""
        Set wb = Workbooks.Open(Pathname & Filename)
        DoWork wb
        wb.Close SaveChanges:=True
        Filename = Dir()
    Loop
End Sub

Sub DoWork(wb As Workbook)
    Dim Search As String
    Dim Replacement As String
    
    Search = " each"
    Replacement = ""
    
    With wb
        'Do your work here
        .Worksheets(1).Columns(3).Replace What:=Search, Replacement:=Replacement, LookAt:=xlPart, MatchCase:=False
    End With
End Sub