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)" <(E-Mail Removed)> wrote in
message news:%(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:89ABE257-1DBC-4B6C-ACE5-(E-Mail Removed)...
>> 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
>
|
Similar Threads
