CODEWITHSUNDEEP
excelvbaloops
Nick
Nick

Reputation: 162

excel VBA - Loop of an array

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

Upvotes: 0

Views: 64

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19712

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

Upvotes: 1

Related Questions