excel VBA - Loop of an array

Question

I made this simple code, or at least tried. But I have one small problem.

When I type For I = 14 To 25 I don't really know what I'm doing. I have a sheet called "Master" and in the range K6:V6 I have every name of every sheet I want to go through. I would like to write something like this: For I = sheets("Master").range("K6:V6") But this does not work, anyone that can help to me to assign the "names" in this array to I?

The rest of the code works as it should, it could be optimized by not having "select" but I don't seem to be able to do it so I took the easy way out. Thank you for your help!

Dim I As Integer
For I = 14 To 25
If Sheets(I).Visible = False Then
    'If sheet = Not visble
    '-----------------------------------------------------------------------------------------------------
    Sheets(I).Visible = True
    AA = Sheets("Master").Range("K6").Value
    Sheets(AA).Select
    ActiveSheet.Unprotect
    ActiveSheet.Range("C3:C120").Copy
    Range("G3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Range("C6:C120").ClearContents
    ActiveSheet.Range("L6:M117").ClearContents
    ActiveSheet.Protect
    Range("A1").Select
    Sheets(I).Visible = False

    '-----------------------------------------------------------------------------------------------------

    Else:
    'If sheet = visble
    '-----------------------------------------------------------------------------------------------------
    AA = Sheets("Master").Range("K6").Value
    Sheets(AA).Select
    ActiveSheet.Unprotect
    ActiveSheet.Range("C3:C120").Copy
    Range("G3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    ActiveSheet.Range("C6:C120").ClearContents
    ActiveSheet.Range("L6:M117").ClearContents
    ActiveSheet.Protect
    Range("A1").Select
    '-----------------------------------------------------------------------------------------------------

End If
Next I

Thank you, but how do I define "I" then? What do I write instead of "Sheets(I).Visible = True"? and how do I stop this? (as I now do with "next I") — Nick
– Nick, Commented Feb 28, 2020 at 7:59

Darren Bartrup-Cook · Accepted Answer · 2020-02-28 08:08:58Z

1

Each Worksheet in a file is held in the Worksheets collection. You can look at each worksheet in the collection in turn and act on it.

Sub Test()

    Dim wrkSht As Worksheet
    Dim shtMaster As Worksheet
    Dim InList As Range
    Dim VisibleSetting As Long

    Set shtMaster = ThisWorkbook.Worksheets("Master") 'Reference to Master worksheet

    'This will look at each worksheet in the worksheets collection and reference it with 'wrkSht'
    For Each wrkSht In ThisWorkbook.Worksheets
        'Look for the worksheet name in the K6:V6 range.
        Set InList = shtMaster.Range("K6:V6").Find(wrkSht.Name, LookIn:=xlValues, LookAt:=xlWhole)

        'If the name is found InList will not be nothing.
        If Not InList Is Nothing Then
            With wrkSht
                VisibleSetting = .Visible 'Remember the visible setting.
                .Visible = xlSheetVisible
                .Unprotect
                .Range("C3:C120").Copy
                .Range("G3").PasteSpecial xlPasteValues
                Union(.Range("C3:C120"), .Range("L6:M17")).ClearContents
                .Visible = VisibleSetting 'Put the visible setting back.
                .Protect
            End With
        End If
    Next wrkSht

End Sub

Further reading:
ThisWorkbook
With...End With Statement
For Each...Next Statement

edited Feb 28, 2020 at 8:08

answered Feb 28, 2020 at 8:02

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nick Over a year ago

THANK YOU! I'm not sure I understand everything but it works! Thank you very much!

braX Over a year ago

I recommend taking the time to learn from the links he included to understand the code instead of just copy/pasting it and using it without really understanding it. It will help you considerably once you need to modify it.

Collectives™ on Stack Overflow

excel VBA - Loop of an array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related