CODEWITHSUNDEEP
excelvba
Nick
Nick

Reputation: 394

Use named range to loop through sheets

I have a named range compiled of 30 string values. Each of the string values is also used as the name of a sheet. I was wondering if it would be possible to loop through each string in the range to edit it's respective sheet. I've gone through some trial and error but am still stuck. Any help would be greatly appreciated.

Option Explicit

Sub NamedRanges()

Dim ws As Worksheet
Dim rng As Variant

rng = Sheets("Teams").Range("TeamList_Full")

For Each ws In rng
    ws.Range("A36").Value2 = 2
    Next ws

End Sub

Upvotes: 1

Views: 64

Answers (2)

Spectral Instance
Spectral Instance

Reputation: 2494

Since you appear to be targeting the same cell on each sheet, you can also achieve your aim by avoiding a loop

Sub NamedRanges()
    Dim sh As Worksheet, rng As Range
    Set rng = Worksheets("Teams").Range("TeamList_Full")
    Set sh = Worksheets(rng.Cells(1).Value2)
    sh.Range("A36").Value2 = 2
    Worksheets(WorksheetFunction.Transpose(rng.Value2)).FillAcrossSheets sh.Range("A36")
End Sub

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166306

You can do it like this:

Sub NamedRanges()

    Dim c As Range
    
    For Each c In ThisWorkbook.Worksheets("Teams").Range("TeamList_Full").Cells
        ThisWorkbook.Worksheets(c.Value).Range("A36").Value2 = 2
    Next c

End Sub

Upvotes: 3

Related Questions