how can I assign a large number of named sheets to an array variable
(varArray).
In otherwords if I have a collection of large number of named worksheets
like "ASD","XZC","BNM",.......etc.
I want to assign them to the array variable varArray, like;
varArray = "ASD","XZC",......
Then I want to use it in the following statement :
For each asheet in activeworkbook.sheets(varArray)
Please notice that :
-I want to limit the search to limited number of sheets of the
activeworkbook, but the number of these sheets are relatively large ( more
than 20 sheets).
-I want to assign the sheets to the varArray in one seperate line, so that I
can increase r decrease the number of sheets anytime
-The activeworkbook contains many sheets, but I want to limit the search (
Done by For ...each )to a certain number out of the total sheet number.
-Are there a limit to the number of sheets used with the statement for...
each?
- I am using Excel 2003 and Excel 2007.
Thank you
--
Nabil A Youssef

The following will create an array and assign sheet names to it:

Sub sheetnames()
Dim ary() As String
ReDim ary(1 To Sheets.Count)
For i = 1 To UBound(ary)
ary(i) = Sheets(i).Name
MsgBox (ary(i))
Next
End Sub

But you do not need the sheet names to refer to, or loop over, sheets; just
use the index.
--
Gary''s Student - gsnu200781


"Nabil" wrote:

how can I assign a large number of named sheets to an array variable
(varArray).
In otherwords if I have a collection of large number of named worksheets
like "ASD","XZC","BNM",.......etc.
I want to assign them to the array variable varArray, like;
varArray = "ASD","XZC",......
Then I want to use it in the following statement :
For each asheet in activeworkbook.sheets(varArray)
Please notice that :
-I want to limit the search to limited number of sheets of the
activeworkbook, but the number of these sheets are relatively large ( more
than 20 sheets).
-I want to assign the sheets to the varArray in one seperate line, so that I
can increase r decrease the number of sheets anytime
-The activeworkbook contains many sheets, but I want to limit the search (
Done by For ...each )to a certain number out of the total sheet number.
-Are there a limit to the number of sheets used with the statement for...
each?
- I am using Excel 2003 and Excel 2007.
Thank you
--
Nabil A Youssef

A structure like this should do what you want; just put your active code
between the With/End With statements using a leading dot for sheet items so
that item will refer to the currently referred to sheet, like my
..Cells(1,1).Value example....

Dim SH As Variant
Dim MySheets As Variant
MySheets = Array("Sheet1", "Sheet4", "Sheet7")
For Each SH In MySheets
With Sheets(SH)
Debug.Print .Cells(1, 1).Value
End With
Next

Rick


"Nabil" wrote in message
...
how can I assign a large number of named sheets to an array variable
(varArray).
In otherwords if I have a collection of large number of named worksheets
like "ASD","XZC","BNM",.......etc.
I want to assign them to the array variable varArray, like;
varArray = "ASD","XZC",......
Then I want to use it in the following statement :
For each asheet in activeworkbook.sheets(varArray)
Please notice that :
-I want to limit the search to limited number of sheets of the
activeworkbook, but the number of these sheets are relatively large ( more
than 20 sheets).
-I want to assign the sheets to the varArray in one seperate line, so that
I
can increase r decrease the number of sheets anytime
-The activeworkbook contains many sheets, but I want to limit the search (
Done by For ...each )to a certain number out of the total sheet number.
-Are there a limit to the number of sheets used with the statement for...
each?
- I am using Excel 2003 and Excel 2007.
Thank you
--
Nabil A Youssef

So there is no confusion with 'shape' of the example names I used, the Array
function assignment statement for example sheet names you posted would be...

MySheets = Array("ASD", "XZC", "BNM")

Just add or remove your sheet names in this statement as needed.

Rick



"Rick Rothstein (MVP - VB)" wrote in
message ...
A structure like this should do what you want; just put your active code
between the With/End With statements using a leading dot for sheet items so
that item will refer to the currently referred to sheet, like my
.Cells(1,1).Value example....

Dim SH As Variant
Dim MySheets As Variant
MySheets = Array("Sheet1", "Sheet4", "Sheet7")
For Each SH In MySheets
With Sheets(SH)
Debug.Print .Cells(1, 1).Value
End With
Next

Rick


"Nabil" wrote in message
...
how can I assign a large number of named sheets to an array variable
(varArray).
In otherwords if I have a collection of large number of named worksheets
like "ASD","XZC","BNM",.......etc.
I want to assign them to the array variable varArray, like;
varArray = "ASD","XZC",......
Then I want to use it in the following statement :
For each asheet in activeworkbook.sheets(varArray)
Please notice that :
-I want to limit the search to limited number of sheets of the
activeworkbook, but the number of these sheets are relatively large (
more
than 20 sheets).
-I want to assign the sheets to the varArray in one seperate line, so
that I
can increase r decrease the number of sheets anytime
-The activeworkbook contains many sheets, but I want to limit the search
(
Done by For ...each )to a certain number out of the total sheet number.
-Are there a limit to the number of sheets used with the statement for...
each?
- I am using Excel 2003 and Excel 2007.
Thank you
--
Nabil A Youssef

It just occurred to me, you might be wanting to change (add or remove)
sheets dynamically in code. The Array function does not really lend itself
to doing that. Examine this code snippet instead to see a method of being
able to actively change the number of referred to sheets...

Dim SH As Variant
Dim MySheets As Variant
Dim SheetNames As String
' Hard coded list of names
MySheets = Split("ASD,XZC,BNM", ",")
For Each SH In MySheets
With Sheets(SH)
Debug.Print .Cells(1, 1).Value
End With
Next
' Dynamically assigned list of names
SheetNames = "ASD,XZC,BNM,XXX,YYY,ZZZ"
MySheets = Split(SheetNames, ",")
For Each SH In MySheets
With Sheets(SH)
Debug.Print .Cells(1, 1).Value
End With
Next

Notice the list of names in the first argument of the Split function is a
single, comma delimited String (no internal quote marks).

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
A structure like this should do what you want; just put your active code
between the With/End With statements using a leading dot for sheet items so
that item will refer to the currently referred to sheet, like my
.Cells(1,1).Value example....

Dim SH As Variant
Dim MySheets As Variant
MySheets = Array("Sheet1", "Sheet4", "Sheet7")
For Each SH In MySheets
With Sheets(SH)
Debug.Print .Cells(1, 1).Value
End With
Next

Rick


"Nabil" wrote in message
...
how can I assign a large number of named sheets to an array variable
(varArray).
In otherwords if I have a collection of large number of named worksheets
like "ASD","XZC","BNM",.......etc.
I want to assign them to the array variable varArray, like;
varArray = "ASD","XZC",......
Then I want to use it in the following statement :
For each asheet in activeworkbook.sheets(varArray)
Please notice that :
-I want to limit the search to limited number of sheets of the
activeworkbook, but the number of these sheets are relatively large (
more
than 20 sheets).
-I want to assign the sheets to the varArray in one seperate line, so
that I
can increase r decrease the number of sheets anytime
-The activeworkbook contains many sheets, but I want to limit the search
(
Done by For ...each )to a certain number out of the total sheet number.
-Are there a limit to the number of sheets used with the statement for...
each?
- I am using Excel 2003 and Excel 2007.
Thank you
--
Nabil A Youssef

this code also replied my question
thank you
--
Nabil A Youssef


"Rick Rothstein (MVP - VB)" wrote:

It just occurred to me, you might be wanting to change (add or remove)
sheets dynamically in code. The Array function does not really lend itself
to doing that. Examine this code snippet instead to see a method of being
able to actively change the number of referred to sheets...

Dim SH As Variant
Dim MySheets As Variant
Dim SheetNames As String
' Hard coded list of names
MySheets = Split("ASD,XZC,BNM", ",")
For Each SH In MySheets
With Sheets(SH)
Debug.Print .Cells(1, 1).Value
End With
Next
' Dynamically assigned list of names
SheetNames = "ASD,XZC,BNM,XXX,YYY,ZZZ"
MySheets = Split(SheetNames, ",")
For Each SH In MySheets
With Sheets(SH)
Debug.Print .Cells(1, 1).Value
End With
Next

Notice the list of names in the first argument of the Split function is a
single, comma delimited String (no internal quote marks).

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
A structure like this should do what you want; just put your active code
between the With/End With statements using a leading dot for sheet items so
that item will refer to the currently referred to sheet, like my
.Cells(1,1).Value example....

Dim SH As Variant
Dim MySheets As Variant
MySheets = Array("Sheet1", "Sheet4", "Sheet7")
For Each SH In MySheets
With Sheets(SH)
Debug.Print .Cells(1, 1).Value
End With
Next

Rick


"Nabil" wrote in message
...
how can I assign a large number of named sheets to an array variable
(varArray).
In otherwords if I have a collection of large number of named worksheets
like "ASD","XZC","BNM",.......etc.
I want to assign them to the array variable varArray, like;
varArray = "ASD","XZC",......
Then I want to use it in the following statement :
For each asheet in activeworkbook.sheets(varArray)
Please notice that :
-I want to limit the search to limited number of sheets of the
activeworkbook, but the number of these sheets are relatively large (
more
than 20 sheets).
-I want to assign the sheets to the varArray in one seperate line, so
that I
can increase r decrease the number of sheets anytime
-The activeworkbook contains many sheets, but I want to limit the search
(
Done by For ...each )to a certain number out of the total sheet number.
-Are there a limit to the number of sheets used with the statement for...
each?
- I am using Excel 2003 and Excel 2007.
Thank you
--
Nabil A Youssef

Thank you for your help , your code could answer my question. It seems that
you are a proffesional in vba.
Good luck
--
Nabil A Youssef


"Rick Rothstein (MVP - VB)" wrote:

A structure like this should do what you want; just put your active code
between the With/End With statements using a leading dot for sheet items so
that item will refer to the currently referred to sheet, like my
..Cells(1,1).Value example....

Dim SH As Variant
Dim MySheets As Variant
MySheets = Array("Sheet1", "Sheet4", "Sheet7")
For Each SH In MySheets
With Sheets(SH)
Debug.Print .Cells(1, 1).Value
End With
Next

Rick


"Nabil" wrote in message
...
how can I assign a large number of named sheets to an array variable
(varArray).
In otherwords if I have a collection of large number of named worksheets
like "ASD","XZC","BNM",.......etc.
I want to assign them to the array variable varArray, like;
varArray = "ASD","XZC",......
Then I want to use it in the following statement :
For each asheet in activeworkbook.sheets(varArray)
Please notice that :
-I want to limit the search to limited number of sheets of the
activeworkbook, but the number of these sheets are relatively large ( more
than 20 sheets).
-I want to assign the sheets to the varArray in one seperate line, so that
I
can increase r decrease the number of sheets anytime
-The activeworkbook contains many sheets, but I want to limit the search (
Done by For ...each )to a certain number out of the total sheet number.
-Are there a limit to the number of sheets used with the statement for...
each?
- I am using Excel 2003 and Excel 2007.
Thank you
--
Nabil A Youssef