Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript Out of Range
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript Out of Range
Sub Test
dim list(0 to 1) list(0) = "Tab1" list(1) = "Tab2" sheets(list).copy End Test -- Regards, Tom Ogilvy "Al" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript Out of Range
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 -----Original Message----- 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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript Out of Range
Many Thanks Tom, your utilization of arrays provides for a
very efficient solution. -----Original Message----- Sub Test dim list(0 to 1) list(0) = "Tab1" list(1) = "Tab2" sheets(list).copy End Test -- Regards, Tom Ogilvy "Al" wrote in message ... 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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript Out of Range
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. -----Original Message----- 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 -----Original Message----- 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. . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript Out of Range
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? On Wed, 22 Sep 2004 06:26:05 -0700, "Al" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subscript out of range | Excel Discussion (Misc queries) | |||
subscript out of range | Excel Programming | |||
Subscript out of range? | Excel Programming | |||
Subscript out of range | Excel Programming | |||
SubScript Out Of Range. | Excel Programming |