Hi, I am having an issue with this macro actually updating all the
sheets in a workbook. I am trying to do a find/replace for each tab in
a workbook - should be simple right?

Here's my code

Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
Range("A1").Select
Cells.Replace What:="FactorName", Replacement:=MyFactor
Next WS

It find's and replaces the first sheet just fine, and as I step
through the code it repeats as many times as their are worksheets (10
times, once for each worksheet) but for only the first sheet is
updated. Not sure what's going on.

I'm sure this is simple, thanks for your help.
Brian

Hi,

Try this, You must qualify the range by using WS.cells. Note that MyFactor
is a variable and your replacing a text value. If the variable is given a
value you will simply end up with empty cells. Did you really mean

WS.Cells.Replace What:="FactorName", Replacement:="MyFactor"

Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
WS.Cells.Replace What:="FactorName", Replacement:=MyFactor
Next WS

Mike


"Bongard" wrote in message
...
Hi, I am having an issue with this macro actually updating all the
sheets in a workbook. I am trying to do a find/replace for each tab in
a workbook - should be simple right?

Here's my code

Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
Range("A1").Select
Cells.Replace What:="FactorName", Replacement:=MyFactor
Next WS

It find's and replaces the first sheet just fine, and as I step
through the code it repeats as many times as their are worksheets (10
times, once for each worksheet) but for only the first sheet is
updated. Not sure what's going on.

I'm sure this is simple, thanks for your help.
Brian

Try it with this slight mod.

Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
With WS
.Cells.Replace What:="FactorName", Replacement:=MyFactor
End With
Next WS




"Bongard" wrote in message
...
Hi, I am having an issue with this macro actually updating all the
sheets in a workbook. I am trying to do a find/replace for each tab in
a workbook - should be simple right?

Here's my code

Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
Range("A1").Select
Cells.Replace What:="FactorName", Replacement:=MyFactor
Next WS

It find's and replaces the first sheet just fine, and as I step
through the code it repeats as many times as their are worksheets (10
times, once for each worksheet) but for only the first sheet is
updated. Not sure what's going on.

I'm sure this is simple, thanks for your help.
Brian

Change "Range("A1").Select" and the "cells" line to one line:
ws.Cells.Replace What:="FactorName", Replacement:=MyFactor

This is a classic VBA problem because "Range" by itself automatically
refers to the active sheet.

Thanks to the prompt response from everyone. This definitely makese
sense and I can see now why it wasn't working. I knew it would be an
easy fix.

Thanks again,
Brian