Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

The matching cell in the unselected worksheets are also replaced when you click the Replace All button in the Find and Replace dialog box in Excel


View products that this article applies to.

Symptoms

When you select multiple, but not all, worksheets in a Microsoft Excel workbook, and then click the Replace All button in the Find and Replace dialog box, matching cells in unselected worksheets may be also be replaced.

↑ Back to the top


Cause

This behavior can occur if the following conditions are true:
  • In Microsoft Office Excel and in earlier versions of Excel, you click Replace or Find on the Edit menu when multiple, but not all, worksheets are selected.

    In Microsoft Office Excel 2007, you click Find & Select in the Editing group on the Home tab, and then click Replace or Find when multiple, but not all, worksheets are selected.
  • You specify Workbook in the Within box on the Replace tab of the Find and Replace dialog box.
Under these conditions, all worksheets in the workbook are searched for matching cells. Therefore, all matching cells are replaced.

↑ Back to the top


Workaround

To work around this issue and replace only cells in selected (grouped) worksheets, follow these steps:
  1. Start Microsoft Excel, and then open the workbook.
  2. Select the group of worksheets.
  3. On the Edit menu, click Replace.

    Note In Excel 2007, click Find & Select in the Editing group on the Home tab, and then click Replace.
  4. On the Replace tab, specify the text that you want in the Find what and Replace with boxes.
  5. Click Options to display the advanced search options. Click the drop-down arrow in the Within box, and then click Sheet.
  6. Click Replace All.

↑ Back to the top


Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

↑ Back to the top


More information

When you select Workbook in the Within box, the options in the Find and Replace dialog box handle the search for multiple selected worksheets in two different ways:
  • The Replace, Find All, and Find Next commands search only the selected worksheets.
  • The Replace All command searches all worksheets in the workbook.
When you select Sheets in the Within box, only the selected sheets are searched for matching cells.

↑ Back to the top


Keywords: KB284881, kbpending, kbbug, kbtshoot, kbprb, kbexpertisebeginner

↑ Back to the top

Article Info
Article ID : 284881
Revision : 5
Created on : 2/1/2007
Published on : 2/1/2007
Exists online : False
Views : 268