Array from row in worksheet
Probably due to this section
'Populate the array
Do While ActiveCell.Offset(0, i).Value < ""
Products(i) = ActiveCell.Offset(0, i).Value
i = i + 1
Loop
The "i" in Products(i) - might be getting to big - worth investigating.
Scott
"Colin Nederkoorn" wrote in message
oups.com...
Hi All,
I am trying to dynamically copy named sheets to a new workbook. The
following works perfectly:
Products = Array("Apple", "Banana", "Orange")
Sheets(Products).Copy
This copies the worksheets Apple, Banana and Orange to a new workbook.
What I am trying to do is dynamically populate the array. I have the
following code to do so:
Dim Products
i = 1
'Get the number of products
Do While ActiveCell.Offset(0, i).Value < ""
i = i + 1
Loop
'Redefine the array size to number of products
ReDim Products((i-1))
'Reset the counter
i = 0
'Populate the array
Do While ActiveCell.Offset(0, i).Value < ""
Products(i) = ActiveCell.Offset(0, i).Value
i = i + 1
Loop
Sheets(Products).Copy
- The code above should redefine the array to add Apple, Banana and
Orange in to position 1, 2, and 3 in the array respectively. The error
message I receive from: Sheets(Products).Copy is:
Run-time error '9':
Subscript out of range
|