Loop through Array of Worksheet CodeNames VBA

Question

I want to use VBA to loop through specific Worksheets in order to unhide them if they are hidden. I want to use CodeNames of Worksheets, not Index or Names.

I have tried something like this but it returns error "object required" on line
If HiddenSheets(sCounter).Visible = xlSheetHidden Then

***New info: I am writing this code in separate workbook. I have created a variable for that workbook WB_Master. Maybe I should somehow call sheets from that Workbook by using this variable?

Dim HiddenSheets As Variant
Dim sCounter As Long

HiddenSheets = Array(Sheet4, Sheet5, Sheet6, Sheet25, Sheet26, Sheet27, Sheet33)

For sCounter = LBound(HiddenSheets) To UBound(HiddenSheets)
    If HiddenSheets(sCounter).Visible = xlSheetHidden Then
        HiddenSheets(sCounter).Visible = xlSheetVisible
    End If
Next sCounter

Your code is working well for me .. Try use False instead of xlSheetHidden and make sure that all the code names in the array are exisiting — YasserKhalil, Aug 16 '18 at 10:36
Codenames are correct.As I said in edited post, these sheets lay in separate workbook from one that holds this code. Maybe that has something to do with error. — Uros Djukic, Aug 16 '18 at 11:01
You cannot directly use codenames of sheets in a different workbook, unless you set a reference to that workbook's project (it must be a macro-enabled format). — Rory, Aug 16 '18 at 11:24
@QHarr, Unfortunately this question is marked as duplicate, the answer provided in the other question needs access to the VBA project object model, Rory's comment worked out in some VBA code would be another way to go — EvR, Aug 16 '18 at 15:21

score 0 · Answer 1 · answered Aug 16 '18 at 13:34

you can still use codenames of sheets in a separate workbook provided you:

store codenames as strings in HiddenSheets
loop through your separate workbook worksheets and check their CodeName being in the HiddenSheets

as follows:

Sub UnHideSheets()
    Dim HiddenSheets As Variant
    HiddenSheets = Array("Sheet4", "Sheet5", "Foglio2", "Sheet25", "Sheet26", "Sheet27", "Sheet33") ' store codenames as strings

    Dim sh As Worksheet
    For Each sh In Workbooks("WB_Master").Worksheets ' loop through your "separate" workbook worksheets (change "WB_Master" to your actual "separate" workbook name)
        If Not IsError(Application.Match(sh.CodeName, HiddenSheets, 0)) Then sh.Visible = xlSheetVisible
    Next
End Sub

@UrosDjukic, any feedback? – DisplayName Sep 09 '18 at 15:11 — DisplayName, Sep 09 '18 at 15:11

Loop through Array of Worksheet CodeNames VBA

1 Answers1