David,

What advantage do you think an array will bring? If you intend to perform a
similar action on every sheet, you will need to iterate through the sheet
names and process each one, and this is true whether the names are on a
worksheet or in an array. In fact, with an array you will need to transfer
the names from the sheet to the array.

Your code could be slightly improves like so, which removes all of the
unnecessary selecting

Sub WSheetSelectL()
Dim i As Long
Dim nRow As Long
Dim Sname$
' selects each sheet name down the list & activates that sheet
i = 2
With Sheets("companies")
Do While Not IsEmpty(.Cells(i, "B").Value)
Sname$ = Cells(i, "B")
' passes the sheet name to a routine that executes more code on
the named sheet
i = i + 1
Loop
End With
End Sub

You could also just loop through the sheet names without storing them, like
so

Dim sh As Worksheet

For Each sh in Activeworkbook.Worksheets
sname$ = sh.Name
' passes the sheet name to a routine that executes more code on
the named sheet
Next sh

but this will process all sheets, but may not be what you want.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"David Shearer" wrote in message
...
I only write VBA once in a blue moon and so I am probably asking the
obvious!

I want to use a list of sheetnames (that will grow over time) held in the
"companies" sheet ,cells b1 to b?? to pass control to a routine that selects
each sheet in turn, carries out some code and then selects the next
sheet....

Obviously I can do this using a routine to find the row of the last name
in column B and then a LOOP:

Sub WSheetSelectL()

' selects each sheet name down the list & activates that sheet
Sheets("companies").Select
Range("b1").Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
Nrow = (ActiveCell.Row)
Sname$ = Cells(Nrow, 2)
' passes the sheet name to a routine that executes more
code on the named sheet
Sheets("companies").Select
ActiveCell.Offset(1, 0).Select
Loop

End Sub

However, I am sure that I could use an array and be a lot
neater/efficient. Trying this has brought me to the limit of my knowledge, I
can populate the array from the cells, but can I use it? Can I h**l!

CAN ANYONE SUGGEST A WAY?