Microsoft Office Tutorials and References
In Depth Information
Finding and Replacing a String in All Open Workbooks
' * * * * *¶
Sub Auto_Close()¶
'Hide all the sheets except the dummy one.¶
RunOnClose¶
End Sub¶
Tip: This macro requires a sheet called 'Macros Disabled', where a customized message to the
user is displayed when the workbook is opened without macros enabled.
Exl
Finding and Replacing a String in All Open Workbooks
Use this macro to perform a quick Find and Replace on all open workbooks.
Example file:
E026.xls
Scenario: Sometimes, due to a company name change, a
text string must be changed in many workbooks. Making the
change in all these workbooks one at a time can be a tedious
and error-prone task. This macro solves the problem by
doing a "global" Find and Replace on all workbooks that are
open when the macro runs.
View the Appendix to learn how to store this procedure
in a Standard module.
Option Explicit¶
' * * * * *¶
Sub FindAndReplace()¶
'Variable declaration¶
Dim sFind As Variant¶
Dim sReplace As Variant¶
Dim Book As Workbook¶
Dim WS As Worksheet¶
'Ask for the text to find¶
sFind = Application.InputBox(Prompt:="Enter the text to find:", _¶
Title:="Find", Type:=2)¶
'Did the user cancel?¶
If TypeName(sFind) = "Boolean" Then Exit Sub¶
If Len(sFind) = 0 Then Exit Sub¶
'Ask for the replacement¶
sReplace = Application.InputBox( _¶
Prompt:="Enter the text to replace with:", Title:="Replace", _¶
Type:=2)¶
 
Search JabSto ::




Custom Search