an array to step through sheets

[Guest]

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 shee
Sheets("companies").Selec
Range("b1").Offset(1, 0).Selec
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 shee
Sheets("companies").Selec
ActiveCell.Offset(1, 0).Selec
Loo

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

 

[Wolf]

Hi David,

I would use the Dymamic Range:
http://www.contextures.com/xlNames01.html#Dynamic

Best regards

Wolf

-----Original 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!

 

[JE McGimpsey]

One way:

First, using Insert/Name/Define, define a workbook name (say Companies).
If you want, you can make it dynamic so that it will automatically
change as you add/delete companies:

Name in Workbook: Companies
Refers to: =Offset('Companies'!$A$1,,,COUNTA('Companies'!$A:$A),1)

then in your macro:

Public Sub DoSheetArray()
Dim vSheets As Variant
Dim i As Long

vSheets = ThisWorkbook.Names("Companies").RefersToRange.Value
For i = 1 To UBound(vSheets, 1)
With Worksheets(vSheets(i, 1))
'do something
.Range("A1") = vSheets(i, 1)
End With
Next i
End Sub

Assigning a range.Value to a variant in this way creates a
two-dimensional 1-based array, equivalent to

Redim vSheets(1 to <# of rows>, 1 to <# of columns>)

Note that you don't need to actually select the sheets as long as you
qualify the range references.

 

[Bob Phillips]

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)

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!