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

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