Find & Replace: Set Search to Sheet Search or Workbook Search Default

Robert Fanning 1 Reputation point
2021-08-25T03:14:56.017+00:00

This Question is about the "Find & Replace" (Ctrl F) functionality. In Sheet OR Workbook.
I have a Workbook with multiple Workbooks containing data.
A macro I use has the Code Line:
Cells.Replace What:="xyz", Replacement:="abc", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Works very well. It's purpose is to Find/Replace in a SPECIFIC SHEET during the macro execution.

PROBLEM:
When manually searched the entire "Workbook" (rather than the Sheet only) it never occurred to me that the "Workbook" selection remained.
So... next time I executed the macro, the above line of code found "xyz" in ALL WORKBOOKS and replaced the "xyz" in ALL WORKBOOKS to "abc."

My Question: How do I set the Find/Replace (using VBA) to execute for only "Sheet" to avoid the macro searching the whole Workbook? Is there a way to set to search "Sheets" (not Workbook) as the default?

Many thanks for your consideration.

0 comments No comments
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.