Array from row in worksheet
Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance
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
.
Relevant Pages
- Re: 32 bit DLL with 64 bit Excel 2010
... "Might be worth trying a copy via a Variant array instead of the clipboard." ... create minimum size workbook for calculation, Change input data, Calculate, store results in array, Loop. ... When I saw the reliability improvment, I thought I had been exhausting some internal Excel resource, and that was the motivation for my original question about using 64-bit Excel. ... Also, at the moment I'm doing a lot of Range.Copy, which uses the sysem clipboard, but I could Range.Copy via your suggestion above. ...
(microsoft.public.excel.programming) - Re: How to copy a workbook
... Next loop so that it matches the items in the array. ... What do you mean by TEMPLATE being an object variable? ... Then it is a variable for a workbook object, or in VBA speak an Object ... I may decide to read the employee name one at a time as I loop through, ...
(microsoft.public.excel.programming) - Re: How to copy a workbook
... Next loop so that it matches the items in the array. ... What do you mean by TEMPLATE being an object variable? ... Then it is a variable for a workbook object, or in VBA speak an Object ... I may decide to read the employee name one at a time as I loop through, ...
(microsoft.public.excel.programming) - Re: Open Workbooks, Used Area on Sheet
... ReDim the wsNamesarray without creating the error subscript out of range ... Dim wbNames(), wsNamesAs String ... Dim i, j As Integer, wb As Workbook, ws As Worksheet ... ReDim wbNames ...
(microsoft.public.excel.programming) - RE: Emergency: Excel 2007: unwanted array inserts itself at startup
... In the navigation window find your personal.xls workbook, ... 10 cells by 10 cells, with a number in each cell; there is a column of "1's", ... When starting any worksheet, the array inserts itself on top of the existing ...
(microsoft.public.excel.setup) |
|
