Method Range of Object Worksheet failing

[mdd16]

Hello.. can someone please help me debug this supposedly simple code.. I am getting error Method Range of Object Worksheet failing

I am trying to hide those sheets where Range ("DCT_Status").Value = "Complete"
Each sheet has a range named "DCT_Status" which is mapped to the sheet.

 For Each ws In ThisWorkbook.Worksheets
   If ws.Visible = xlSheetVisible And ws.Range("DCT_Status").Value = "Complete" Then
        ws.Visible = xlSheetHidden
   End If
   Next ws

 

[Osvaldo Palmeiro]

Hi. Try this code, please.

Sub HideUnhideSheets()
 Dim ws As Worksheet
  For Each ws In ThisWorkbook.Worksheets
   On Error Resume Next 'to avoid error if some sheet doesn't have that named range
   ws.Visible = ws.Range("DCT_Status").Value <> "Complete"
   On Error GoTo 0
  Next ws
End Sub

 

[Micron]

I believe that if you are doing this from another sheet, or a standard module, you have to preface the worksheet range name with the sheet name. Maybe the reference is like Range("'" & ws.Name & "!DCT_Status")
I can get a reference to a range using that syntax but since I don't have the same range name on every sheet I can't fully test it.

 

[MARK858]

or a standard module, you have to preface the worksheet range name with the sheet name

Doesn't need it for me

ws.Range("DCT_Status").Value

works fine with just the ws variable

 

[Micron]

I didn't test it, so that's why I said "I believe".

 

[mdd16]

Thanks Micron, Thanks Mark.. error was on my side. I had spelling error on the range name and that of course caused the trouble. Thanks for helping out