replace in multiple sheets
 

Hi, I am trying to find a specific text in a worksheet and then put a
certaint text in cells next to where first text is found. And I want to
do it in all worksheets in the file I have. Here is the code that I came
up with but this only works on the active worksheet that I am in... I
think it should work on all worksheets in the file. Does anyone know
why it would do that...

Sub macro2

Dim wks As Worksheet
Dim txtlookup As String
Dim txtchange As String

txtlookup = InputBox("Enter Text to Lookup:", "Enter Text")
txtchange = InputBox("Enter Text to change to:", "Enter Text")
For Each wks In ThisWorkbook.Sheets
For Each cell In Range("A1:A2000")
If cell.Value = txtlookup Then
cell.Offset(0, 1).Value = txtchange
End If
Next cell
Next wks
End Sub

Thanks, Jay


--
sa02000
------------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=568789

replace in multiple sheets
 

Range("A1:A2000")
will refer to the active sheet (if the code is in a general module).

So try:

For Each cell In wks.Range("A1:A2000")

But you may want to record a macro when you do Edit|Replace.

Then loop through all the sheets using that code.

sa02000 wrote:

Hi, I am trying to find a specific text in a worksheet and then put a
certaint text in cells next to where first text is found. And I want to
do it in all worksheets in the file I have. Here is the code that I came
up with but this only works on the active worksheet that I am in... I
think it should work on all worksheets in the file. Does anyone know
why it would do that...

Sub macro2

Dim wks As Worksheet
Dim txtlookup As String
Dim txtchange As String

txtlookup = InputBox("Enter Text to Lookup:", "Enter Text")
txtchange = InputBox("Enter Text to change to:", "Enter Text")
For Each wks In ThisWorkbook.Sheets
For Each cell In Range("A1:A2000")
If cell.Value = txtlookup Then
cell.Offset(0, 1).Value = txtchange
End If
Next cell
Next wks
End Sub

Thanks, Jay

--
sa02000
------------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=568789

--

Dave Peterson

replace in multiple sheets
 

Dave, Thanks. I will try your solution. Also, I found out that adding
the following line after worksheet FOR statement does the trick too.

wks.activate

which really does the same thing that you are saying.

Thanks Again.
Jay


--
sa02000
------------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=568789

replace in multiple sheets
 

Almost the same...

It actually activates that worksheet. wks.range(...) doesn't depend on the
selection of sheets.

And if the code is behind a worksheet, it still won't work correctly.

It's not usually a good idea to select sheets, select ranges or activate
workbooks. It just slows things down and makes it more difficult to debug the
code (my opinion).

sa02000 wrote:

Dave, Thanks. I will try your solution. Also, I found out that adding
the following line after worksheet FOR statement does the trick too.

wks.activate

which really does the same thing that you are saying.

Thanks Again.
Jay

--
sa02000
------------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=568789

--

Dave Peterson