CODEWITHSUNDEEP
excelvba
stonk
stonk

Reputation: 315

excel vba - iterate through specific sheets in range

I would like to iterate through a list of sheets where the list is determined by a Range. If I hard-code the list everything is fine. what I'd like is to refer to a range that contains the sheet names (as it's variable).

Set mySheets = Sheets(Array("sheetOne", "sheetTwo", "sheetThree"))

With ActiveWorkbook
    For Each ws In mySheets
    'do the stuff here
    Next ws
End With

so something like this:

Set mySheets = Sheets(Range("A1:E1"))

Any ideas?

Upvotes: 1

Views: 872

Answers (4)

DisplayName
DisplayName

Reputation: 13386

you could do like this:

Sub DoThat()
    Dim cell As Range

    For Each cell In Range("A1:E1").SpecialCells(xlCellTypeConstants)
        If Worksheets(cell.Value2) Is Nothing Then
            MsgBox cell.Value2 & " is not a sheet name in " & ActiveWorkbook.Name & " workbook"
        Else
            With Worksheets(cell.Value2)
                'do the stuff here
                Debug.Print .Name
            End With
        End If
    Next
End Sub

or the other way around:

Sub DoThatTheOtherWayAround()
    Dim sht As Worksheet

    For Each sht In Worksheets
        If Not IsError(Application.Match(sht.Name, Range("A1:E1"), 0)) Then
            'do the stuff here
            Debug.Print sht.Name
        End If
    Next
End Sub

but in this latter case, you wouldn't be advised in case of any A1:E1 value not corresponding to actual sheet name

Upvotes: 0

Louis
Louis

Reputation: 3632

This will work:

Sub MySub()
    On Error Resume Next
    Set mySheets = Sheets(removeEmpty(rangeToArray(Range("A1:E1"))))
    If Err.Number = 9 Then
        MsgBox "An error has occurred. Check if all sheet names are correct and retry.", vbCritical
        Exit Sub
    End If
    On Error GoTo 0

    With ActiveWorkbook
        For Each ws In mySheets
        'do the stuff here
        Next ws
    End With
End Sub

'This will transpose a Range into an Array()
Function rangeToArray(rng As Range) As Variant
    rangeToArray = Application.Transpose(Application.Transpose(rng))
End Function

'This will remove empty values and duplicates
Function removeEmpty(arr As Variant) As Variant
    Dim result As New Scripting.Dictionary
    Dim element As Variant

    For Each element In arr
        If element <> "" And Not result.Exists(element) Then
            result.Add element, Nothing
        End If
    Next

    removeEmpty = result.Keys
End Function

This will load dynamically Sheets contained in your Range.

Edit

  • Added Function removeEmpty(...) to remove empty values and duplicates.

Note: the Function rangeToArray() is needed to return data in Array() format.

I hope this helps.

Upvotes: 1

Tony M
Tony M

Reputation: 1762

I demonstrate the code below which does what you want using an animated gif (click for better detail)

enter image description here

Option Explicit
Sub iterateSheets()
Dim sh As Worksheet, shName As String, i As Integer
i = 0
For Each sh In ThisWorkbook.Worksheets
    shName = sh.Range("A1").Offset(i, 0)
    Worksheets(shName).Range("A1").Offset(i, 0).Font.Color = vbRed
    i = i + 1
Next
End Sub

Upvotes: 0

Lars
Lars

Reputation: 142

I would provide this solution, which does load the sheetnames into an array: Notice that you have to transpose the Data if the values are ordered horizontal.

Public Sub test()
Dim mySheet As Variant
Dim sheet As Variant

mySheet = Application.Transpose(Tabelle1.Range("A1:E1").Value) 'load your Values into an Array, of course the range can also be dynamic

For Each sheet In mySheet
    Debug.Print sheet 'print the sheet names, just for explaining purposes
    'it may be necessary to use CStr(sheet) if you want to refer to a sheet like Thisworkbook.Worksheets(CStr(sheet))
    'Do something
Next sheet

Erase mySheet 'delete the Array out of memory
End Sub

Upvotes: 0

Related Questions