Subscript Out of Range

[Al]

I'm trying to copy out worksheets Tab1 and Tab2 but am
receiving a subscript out of range error. The Worksheets
do exist and the code is attached below.

I've simplified this problem, as I do need a Variable
to denote which worksheets will be copied out.

(Otherwise I could have used
Sheets(Array("Tab1","Tab2")).Copy
which works)


Sub test()
Dim s As String

s = """" & "Tab1" & """" & ", " & """" & "Tab2" & """"
Sheets(Array(s)).Copy
End Sub

Thanks in advance.

 

[Tom Ogilvy]

Sub Test
dim list(0 to 1)
list(0) = "Tab1"
list(1) = "Tab2"
sheets(list).copy
End Test

 

[Pete McCosh]

Al,
the problem is that the SHEETS(ARRAY(..)) is looking for
an array of values and you are sending a single value
(your variable, "s") which has been manipulated to equal
the same value as the basic ARRAY(..), but isn't an array.

Are there only ever two sheets to copy, and where do the
names come from? If there are always the same number of
sheets, you could just use two variables, or make "s" an
array and the two sheet names subscripts of this:

Sheets(array(s,t)).copy
Sheets(array(s(1),s(2))).copy

Cheers, Pete

 

[Al]

Many Thanks Tom, your utilization of arrays provides for a
very efficient solution.

 

[Al]

Pete:

Thanks for pointing out the flaw in my code. I now know
what I did wrong and have been able to fix it with yours
and Toms help.

I'm now using a countif statement to determine the upper
bound of my array (the solution Tom provided)

And then using the redim statement and a loop to fill it
accordingly.

 

[Myrna Larson]

Since you probably don't have a sheet named "Tab1","Tab2", the code fails.

Your code creates a single string consisting of a list of tab names, delimited
by commas. It doesn't create an array with one tab name in each element. As
you know, it's the array statement that does that.

What is the problem with using the statement that you know works?