VBA Find and Replace on Very Hidden Sheets

[nitrammada]

Hi Everyone,
I'm trying to do a find and replace on over 300 very hidden sheets on data I have in column L.
I big shout of thanks to Rick Rothstein who's code works perfectly on a visible sheet.
Others have assisted me with other routines on very hidden sheets (thanks to Crystalyser) and it works perfectly so I figured I could just swap Rick Rothstein's code and it would run.
But of course I figured wrong. My code is below if anyone could help, as always, I'm always grateful.

Sub Replacements3()

Dim lWS_Visible_State As Long
Dim ws As Worksheet, MyRng As Range
Dim X As Long, FindThese As Variant, ReplaceWith As Variant

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'This works on a single visible sheet only
    For Each ws In Worksheets
        ws.Unprotect
        
        If ws.Range("A1") = "L2" And ws.Range("M6") = "H5" Then
            lWS_Visible_State = ws.Visible
            ws.Visible = xlSheetVisible
        
                FindThese = Array("36001", "37001")
                ReplaceWith = Array("36000", "37000")
                For X = LBound(FindThese) To UBound(FindThese)
                Columns("L:L").Replace FindThese(X), ReplaceWith(X), xlWhole, , True, , False, False
                Next
                
            ws.Visible = lWS_Visible_State
        End If
        
        ws.Protect
    Next ws

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[rondeondo]

hi nitrammada,
My first thought would be to move the line that sets the sheet to visible out to before the if. I am being a little lazy in not setting up a file to fit your described setup. But my logic is that if the code works perfectly well with visible sheets that you need to make the very hidden sheets visible first, then run the code then hide them at the end. I see your code does this but it is after the test of contents of A1 and M6. Being hidden the code may not be "seeing" this data.

 

[Alex Blakenburg]

Just change this line:
And add ws. to the front

Columns("L:L").Replace FindThese(X), ReplaceWith(X), xlWhole, , True, , False, False

To this

ws.Columns("L:L").Replace FindThese(X), ReplaceWith(X), xlWhole, , True, , False, False

 

[nitrammada]

hi nitrammada,
My first thought would be to move the line that sets the sheet to visible out to before the if. I am being a little lazy in not setting up a file to fit your described setup. But my logic is that if the code works perfectly well with visible sheets that you need to make the very hidden sheets visible first, then run the code then hide them at the end. I see your code does this but it is after the test of contents of A1 and M6. Being hidden the code may not be "seeing" this data.

Hi Rondeondo,
Thank you for responding, I see what your saying and it sounds logical what you say, I'll try it. Thanks again.

 

[nitrammada]

Just change this line:
And add ws. to the front

Columns("L:L").Replace FindThese(X), ReplaceWith(X), xlWhole, , True, , False, False

To this

ws.Columns("L:L").Replace FindThese(X), ReplaceWith(X), xlWhole, , True, , False, False

Hello Alex,
Thank you so much for your response, you were spot on, I put the ws in front of the columns and it worked first time perfectly, just what I was trying to achieve, thank you for sharing your expertise, I really appreciate it.
Kind regards
Adam

 

[Alex Blakenburg]

No problem. Thank you for the feedback. Glad we were able to help.