hide sheets in named range array

Question

i have a dynamic named range which provides the sheets names in a list (let's say A2 = Austria, A3 = Germany, A4 = Poland) This list can vary in size depending on the user so I have an offset in the named range to capture all the cells in column A with a name in it.

I would like to use an IF statement to veryhide certain sheets based on this named range.

something like:

Public Sub test()
    Sheets(Array(Range("MyRange"))).Visible = xlVeryHidden
End Sub

with the above i get the following error: Run-time error '13': Type mismatch

any suggestions please.

Thank you Paul

Answer 1

Sheets cannot take an array of names as it's argument, it's declared as Sheets(index), so you'll need to loop through the names and hide the sheets one-by-one unfortunately.

Try:

Sub test()
    Dim rngCell As Range
    For Each rngCell In Range("MyRange")
        Sheets(rngCell.Value).Visible = xlSheetVeryHidden
    Next rngCell
End Sub

Answer 2

You could just loop on the Named range

Sub Test()
dim oCell as range
for each oCell in Range("MyRange)
Sheets(oCell.value).visible=xlVeryHidden
next oCell
end sub

Answer 3

You can pass an array to Sheets(), but if you're getting it from a vertical range you need to transpose it first.

This works for me:

ThisWorkbook.Sheets(Application.Transpose(Sheet3.Range("SHEETS").Value)).Visible = _ xlSheetHidden

Where SHEETS contains the values "Sheet1" and "Sheet2". Unfortunately for some reason it only seems to work with xlSheetHidden and not with xlSheetVeryHidden ....

Tim