For Each Worksheet Problem
 

I am writing a macro to find and replace a list of variables in each sheet
(except one!) of a given workbook. The list is on a worksheet named
TagList. I have written the macro below, but it will only perform the
find/replace on the active sheet (including TagList). Why is it not cycling
through each sheet and why is it working on TagList? Using XL2002 SP3 and
Windows XP. Thanks in advance for your help.

Option Explicit

Sub PITagReplace()

Dim wks As Worksheet
Dim wksList As Worksheet
Dim x, xFind, xReplace

Set wksList = Worksheets("TagList")

For x = 3 To 18
With Worksheets("TagList")
xFind = .Range("A" & x).Value
xReplace = .Range("B" & x).Value
End With

For Each wks In ThisWorkbook.Worksheets
If wks.Name < wksList.Name Then _
Cells.Replace What:=xFind, Replacement:=xReplace
Next wks
Next x

End Sub

For Each Worksheet Problem
 

For Each wks In ThisWorkbook.Worksheets
If wks.Name < wksList.Name Then _
Cells.Replace What:=xFind, Replacement:=xReplace
Next wks

the unqualified cells refers to the activesheet or if it is in a sheet level
module, to the sheet containing the code. qualify it with wks

For Each wks In ThisWorkbook.Worksheets
If wks.Name < wksList.Name Then _
wks.Cells.Replace What:=xFind, Replacement:=xReplace
Next wks

--
Regards,
Tom Ogilvy

"Mickey Authement" wrote in message
...
I am writing a macro to find and replace a list of variables in each sheet
(except one!) of a given workbook. The list is on a worksheet named
TagList. I have written the macro below, but it will only perform the
find/replace on the active sheet (including TagList). Why is it not
cycling
through each sheet and why is it working on TagList? Using XL2002 SP3 and
Windows XP. Thanks in advance for your help.

Option Explicit

Sub PITagReplace()

Dim wks As Worksheet
Dim wksList As Worksheet
Dim x, xFind, xReplace

Set wksList = Worksheets("TagList")

For x = 3 To 18
With Worksheets("TagList")
xFind = .Range("A" & x).Value
xReplace = .Range("B" & x).Value
End With

For Each wks In ThisWorkbook.Worksheets
If wks.Name < wksList.Name Then _
Cells.Replace What:=xFind, Replacement:=xReplace
Next wks
Next x

End Sub

For Each Worksheet Problem
 

Doh! I should have known that.

Thanks Tom!

"Tom Ogilvy" wrote in message
...
For Each wks In ThisWorkbook.Worksheets
If wks.Name < wksList.Name Then _
Cells.Replace What:=xFind, Replacement:=xReplace
Next wks

the unqualified cells refers to the activesheet or if it is in a sheet
level
module, to the sheet containing the code. qualify it with wks

For Each wks In ThisWorkbook.Worksheets
If wks.Name < wksList.Name Then _
wks.Cells.Replace What:=xFind, Replacement:=xReplace
Next wks

--
Regards,
Tom Ogilvy

"Mickey Authement" wrote in message
...
I am writing a macro to find and replace a list of variables in each
sheet
(except one!) of a given workbook. The list is on a worksheet named
TagList. I have written the macro below, but it will only perform the
find/replace on the active sheet (including TagList). Why is it not
cycling
through each sheet and why is it working on TagList? Using XL2002 SP3
and
Windows XP. Thanks in advance for your help.

Option Explicit

Sub PITagReplace()

Dim wks As Worksheet
Dim wksList As Worksheet
Dim x, xFind, xReplace

Set wksList = Worksheets("TagList")

For x = 3 To 18
With Worksheets("TagList")
xFind = .Range("A" & x).Value
xReplace = .Range("B" & x).Value
End With

For Each wks In ThisWorkbook.Worksheets
If wks.Name < wksList.Name Then _
Cells.Replace What:=xFind, Replacement:=xReplace
Next wks
Next x

End Sub