Array from row in worksheet
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 |
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 |
All times are GMT +1. The time now is 03:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com