For Each Worksheet Problem

[Mickey Authement]

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

 

[Tom Ogilvy]

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

 

[Mickey Authement]

Doh! I should have known that.

Thanks Tom!

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