Global find and replace in entire workbook

[merskamp]

I can do a find and replace in a worksheet but is there a quick way to apply it to an entire workbook - I have 16 sheets of data and doing a one by one sheet replace is really tedious.

 

[Smitty]

Welcome to the Board!

How's this:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> ReplaceAll()
    <SPAN style="color:#00007F">Dim</SPAN> cursht <SPAN style="color:#00007F">As</SPAN> Worksheet, ws <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, y <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    
        <SPAN style="color:#00007F">Set</SPAN> cursht = ActiveSheet
        
        x = InputBox("Replace What?", "Replace What?", ActiveCell.Value)
            <SPAN style="color:#00007F">If</SPAN> x = "" <SPAN style="color:#00007F">Then</SPAN> ReplaceAll
        y = InputBox("Replace With?", "Replace <SPAN style="color:#00007F">With</SPAN>?", ActiveCell.Value)
                
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets
            <SPAN style="color:#00007F">With</SPAN> ws
                .Activate
                <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
                .Cells.Replace _
                    What:=x, _
                    Replacement:=y, _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByRows, _
                    MatchCase:=<SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">End</SPAN> With
        <SPAN style="color:#00007F">Next</SPAN> ws
        
        cursht.Activate
        
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty

 

[Peter_SSs]

I can do a find and replace in a worksheet but is there a quick way to apply it to an entire workbook - I have 16 sheets of data and doing a one by one sheet replace is really tedious.

This worked for me:
1. Select all sheets (right-click any sheet tab and choose 'Select All Sheets'.
2. Select all cells (click the 'Cells' box - the grey rectangle to the left of column A heading).
3. Edit|Replace... complete the Find what: and Replace with: boxes|Replace All|Close
4. Right click a sheet tab and choose 'Ungroup Sheets'

 

[erik.van.geit]

Hi, Peter,

good to know that one
I never thought about grouping sheets to replace items

have a nice sunday, you made mine :wink:
Erik

Australia, your sunday could be over by now