Run macro on all sheets except two

[Vlad717]

I must be overlooking something as no matter what I try the Replace function keeps replacing the string on the sheet that I want it to exclude.

I've tried with and without the quotes around the sheet names. I'm stumped

Please Login or Register  to view this content.

[bakerman2]

Try this where CodeName is the name between parenthesis.

Please Login or Register  to view this content.

[Vlad717]

Do you mean like so?

Please Login or Register  to view this content.

It still replaces everything in the workbook including the Backup and Options sheet.

[bakerman2]

Sorry, my bad. It's the name NOT between parenthesis.

In this case Blad1

[Vlad717]

Gotcha. Appreciate you explaining it. I end up with the below in that case and its replacing everything in the workbook lol

Please Login or Register  to view this content.

[bakerman2]

Please Login or Register  to view this content.

[Vlad717]

Wops, that was left over from trying different things. I just tried it with 'And' as well and I get the same result

[shaiz]

Sub ReplacingTest()

Dim Sh As Object

For Each Sh In ActiveWorkbook.Worksheets
If Sh.Name <> "Options" And Sh.Name <> "BackUp" Then
Sh.Cells.Replace What:="VLOOKUP(""19A19"",Options!$F$9:$H$45,3,FALSE)", Replacement:="123456789", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End If
Next Sh

End Sub

[Vlad717]

Please Login or Register  to view this content.

I don't believe there is any change in the code from what was initially posted? It will run with or without the quotes okay

[bakerman2]

This is taking too long, post example file so we can test for ourselves.
This works without a doubt.

[Vlad717]

I think there is a larger issue at play here. I stripped down the book to nothing but a few borders but the entire sheet structure is still there. Its attached (but somewhat unneeded as its working there). I closed and reopened the example book and it doesn't replace the vlookup string like it is intended... (which is good). I literally copy/paste the code into the exact same module with the exact same sheet names of what was just stripped and it replaces it.... wtf

Edit. I should note that I did not copy the workbook or copy the sheets. It was only 'save as' and I also tried changing the file name of the problem book to no avail.

[bakerman2]

Next time please post an example file without vba password protection.
The code posted in Post#6 works just fine in your file so I don't see what more can be done.

[Vlad717]

Shoot, sorry! I forgot to take that part out.

Yeah it seems to work fine in a new workbook but for some reason in this one, even in its own module... it just wants to replace everything. Very strange. Appreciate the help though!

[bakerman2]

Without the actual file to test I'm a bit in the dark I'm afraid.