Showing posts from August, 2017

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…