Selection Range of sheets

[Dannyh1]

H

i I think this is a simple one

I am trying to do an activity on range of sheets using "For Each" (sheet in range of sheets)

As a Range would be defined as A1:A100 for eg., how would do you write this for a Range of sheets

1st sheet 'KENNETH WRIGHT', last sheet 'SANDRA WOODS'

Many thanks

 

[Jonmo1]

Try something like

Dim c As Range, ws As Worksheet
'MasterSheet is the sheet holding the range of sheet names.
For Each c in Sheets("MasterSheet").Range("A1:A100")
    Set ws = Sheets(c.Value)
    'Do stuff using ws as the sheet reference.
    ws.Range("A1").Value = "This is Sheet " & ws.Name
Next c

 

[MickG]

Hi, Perhaps:-

[COLOR=navy]Sub[/COLOR] MG03Aug59
[COLOR=navy]Dim[/COLOR] Shts, Sht
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
Shts = Array("KENNETH WRIGHT", "MICKG", "SANDRA WOOD")
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Sht [COLOR=navy]In[/COLOR] Shts
    [COLOR=navy]With[/COLOR] Sheets(Sht)
        [COLOR=navy]Set[/COLOR] Rng = .Range("A1:A100")
            [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
                Dn = Sht
                '[COLOR=green][B]Do something Here !![/B][/COLOR]
            [COLOR=navy]Next[/COLOR] Dn
    [COLOR=navy]End[/COLOR] With
 [COLOR=navy]Next[/COLOR] Sht
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Dannyh1]

great - thanks guys