2

I have a table called "HideSheets" (single column) to which sheets get added or sometimes removed. How do I reference the table in 

Sub HideSheets()
    Dim cell As Range
    On Error Resume Next
    For Each cell In Sheets("Index").Range("HideSheets")
        ' Hide sheets
        Sheets(cell.Value).Visible = False

End Sub
Community
  • 1
  • 1
Brisbanebob
  • 41
  • 1
  • 3
  • 1
    `For Each` must also have a `Next` at the bottom of the code to be looped (I.e just before `End Sub`) – CallumDA Feb 14 '17 at 23:43

1 Answers1

2

For Each must also have a Next command

Sub HideSheets()
    Dim r As Range
    On Error Resume Next
    For Each r In Sheets("Index").Range("HideSheets")
        ' Hide sheets
        Sheets(r.Value).Visible = xlSheetHidden
    Next r
End Sub

Rather than using On Error Resume Next, check for conditions which might cause an error and deal with them appropriately. For example, return an error to the user which explains that the following error(s) have occurred:

  1. User attempted to hide all sheets in a workbook
  2. r.Value is not a valid sheet name.
CallumDA
  • 12,025
  • 6
  • 30
  • 52
  • Further reading on error handling here: http://stackoverflow.com/questions/1038006/good-patterns-for-vba-error-handling – CallumDA Feb 15 '17 at 00:31