Macro to replace in two worksheets

[yshridhar]

Hi everybody
The following macro i recorded to replace a value in sheet1 and sheet2.

Sub replas()
Sheets(Array("Sheet1", "Sheet2")).Select
Sheets("Sheet2").Activate
Cells.Replace What:="10", Replacement:="a", LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
Range("B1").Select
End Sub

But when i try to run it, it is replacing only in Sheet2. How to modify it?
Any suggestions. Thanks to all
With regards
Sreedhar

 

[Jim Cone]

Here is one way...
'--
Sub replas_R1()
Sheets("Sheet1").Cells.Replace What:="10", Replacement:="a", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Sheets("Sheet2").Cells.Replace What:="10", Replacement:="a", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Range("B1").Select
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"yshridhar" <[email protected]>
wrote in message
Hi everybody
The following macro i recorded to replace a value in sheet1 and sheet2.

Sub replas()
Sheets(Array("Sheet1", "Sheet2")).Select
Sheets("Sheet2").Activate
Cells.Replace What:="10", Replacement:="a", LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
Range("B1").Select
End Sub

But when i try to run it, it is replacing only in Sheet2. How to modify it?
Any suggestions. Thanks to all
With regards
Sreedhar

 

[yshridhar]

Thanks Jim for your suggestion. Is there any way to do it by selecting all
the sheets at once.
regards
Sreedhar

 

[Jim Cone]

Not that I know of.
There is very little in VBA that can be accomplished on multiple sheets
with only one iteration. My experience has been that it consumes all
of your random access memory while trying.
Jim Cone



"yshridhar"
<[email protected]>
wrote in message
Thanks Jim for your suggestion. Is there any way to do it by selecting all
the sheets at once.
regards
Sreedhar

 

[Gord Dibben]

Sub replas()
Sheets(Array("Sheet1", "Sheet2")).Select
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Cells.Replace What:="10", Replacement:="a", LookAt:=xlWhole, _
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B1").Select
Next ws
Sheets("Sheet1").Select
End Sub


Gord Dibben MS Excel MVP

 

[yshridhar]

Thanks Gord.
Regards
Sreedhar

Sub replas()
Sheets(Array("Sheet1", "Sheet2")).Select
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Cells.Replace What:="10", Replacement:="a", LookAt:=xlWhole, _
SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B1").Select
Next ws
Sheets("Sheet1").Select
End Sub


Gord Dibben MS Excel MVP