I need help setting up an array to read data into.

I would like to read data from 3 columns on one sheet into an array and
then copy that information into another sheet.

The Array/Matrix will be 3 columns wide, but the number of rows will
need to be dynamic.

Can anyone help?

TIA
E

If you want to copy and paste, you should just use the copy and paste
methods, and not worry about matrices. But if you need a dynamic
matrix, I think you should look into "Redim Preserve". Redim will
"Dim" the variable with new subscripts. For example,

Dim var (10) as long
[Use var in code. Decide that you need more than 10 long words]
Redim var (20) as long

The Redim has changed the size of the array. Unfortunately, it has
also lost the 10 values in the previous array. To save these use the
"Preserve" keyword:

Dim var (10) as long
[Use var in code. Decide that you need more than 10 long words]
Redim Preserve var (20) as long

Now, var has changed size, and the first 10 values remain unchanged.

The order of the subscripts is important in a matrix. If you are going
to change a matrix, change only the last subscript. For example:

Dim Mat (10, 5) as long
[Use var in code. Decide that you need more than 10 long words]
Redim Preserve Mat (10, 10) as long

Hope this helps,
Dom



Egon wrote:
> I need help setting up an array to read data into.
>
> I would like to read data from 3 columns on one sheet into an array and
> then copy that information into another sheet.
>
> The Array/Matrix will be 3 columns wide, but the number of rows will
> need to be dynamic.
>
> Can anyone help?
>
> TIA
> E

Hi
Here is some code.. However you can accomplish this more succintly by just
copying and pasting in code.

Public Function CopyFromOneSheetToAnother(ByVal sOne$, ByVal sOther$) As
Boolean

Dim aValues() As Variant
Dim i%, lNumRows&
Dim wsOne As Worksheet
Dim wsOther As Worksheet

Set wsOne = ThisWorkbook.Worksheets(sOne)
Set wsOther = ThisWorkbook.Worksheets(sOther)

lNumRows = wsOne.Cells.SpecialCells(xlCellTypeLastCell).Row
ReDim aValues(1 To lNumRows, 1 To 3)
For i = 1 To wsOne.Cells.SpecialCells(xlCellTypeLastCell).Row
aValues(i, 1) = wsOne.Cells(i, 1).Value
aValues(i, 2) = wsOne.Cells(i, 2).Value
aValues(i, 3) = wsOne.Cells(i, 3).Value
Next i

With wsOther
With .Range(.Cells(1, 1), .Cells(lNumRows, 3))
.Value = aValues
End With
End With

End Function


"Egon" wrote:

> I need help setting up an array to read data into.
>
> I would like to read data from 3 columns on one sheet into an array and
> then copy that information into another sheet.
>
> The Array/Matrix will be 3 columns wide, but the number of rows will
> need to be dynamic.
>
> Can anyone help?
>
> TIA
> E
>
>

My problem is that I don't want to copy and paste because the data that
is being copied is in different locations throughout the spread sheet.
This is what we are trying to make easier for some people now. Some
rows have data that needs to be copied, some rows do not. I need to be
able to copy only the rows that are bold, filtering out 3 columns in
the source, and pasting into a new worksheet so that it can be set off
as a report each week.

Thanks.
E

(E-Mail Removed) wrote:
> If you want to copy and paste, you should just use the copy and paste
> methods, and not worry about matrices. But if you need a dynamic
> matrix, I think you should look into "Redim Preserve". Redim will
> "Dim" the variable with new subscripts. For example,
>
> Dim var (10) as long
> [Use var in code. Decide that you need more than 10 long words]
> Redim var (20) as long
>
> The Redim has changed the size of the array. Unfortunately, it has
> also lost the 10 values in the previous array. To save these use the
> "Preserve" keyword:
>
> Dim var (10) as long
> [Use var in code. Decide that you need more than 10 long words]
> Redim Preserve var (20) as long
>
> Now, var has changed size, and the first 10 values remain unchanged.
>
> The order of the subscripts is important in a matrix. If you are going
> to change a matrix, change only the last subscript. For example:
>
> Dim Mat (10, 5) as long
> [Use var in code. Decide that you need more than 10 long words]
> Redim Preserve Mat (10, 10) as long
>
> Hope this helps,
> Dom
>
>
>
> Egon wrote:
> > I need help setting up an array to read data into.
> >
> > I would like to read data from 3 columns on one sheet into an array and
> > then copy that information into another sheet.
> >
> > The Array/Matrix will be 3 columns wide, but the number of rows will
> > need to be dynamic.
> >
> > Can anyone help?
> >
> > TIA
> > E

Are you saying I sould just copy and paste on a per line basis between
the two workbooks and that it would be faster to do so?

Alok wrote:
> Hi
> Here is some code.. However you can accomplish this more succintly by just
> copying and pasting in code.
>
> Public Function CopyFromOneSheetToAnother(ByVal sOne$, ByVal sOther$) As
> Boolean
>
> Dim aValues() As Variant
> Dim i%, lNumRows&
> Dim wsOne As Worksheet
> Dim wsOther As Worksheet
>
> Set wsOne = ThisWorkbook.Worksheets(sOne)
> Set wsOther = ThisWorkbook.Worksheets(sOther)
>
> lNumRows = wsOne.Cells.SpecialCells(xlCellTypeLastCell).Row
> ReDim aValues(1 To lNumRows, 1 To 3)
> For i = 1 To wsOne.Cells.SpecialCells(xlCellTypeLastCell).Row
> aValues(i, 1) = wsOne.Cells(i, 1).Value
> aValues(i, 2) = wsOne.Cells(i, 2).Value
> aValues(i, 3) = wsOne.Cells(i, 3).Value
> Next i
>
> With wsOther
> With .Range(.Cells(1, 1), .Cells(lNumRows, 3))
> .Value = aValues
> End With
> End With
>
> End Function
>
>
> "Egon" wrote:
>
> > I need help setting up an array to read data into.
> >
> > I would like to read data from 3 columns on one sheet into an array and
> > then copy that information into another sheet.
> >
> > The Array/Matrix will be 3 columns wide, but the number of rows will
> > need to be dynamic.
> >
> > Can anyone help?
> >
> > TIA
> > E
> >
> >

Hi Egon,
No copying and pasting on a per line basis is always the slower option. Your
best bet is to copy information from various parts of the workbook and moving
it into an array and then pasting it all at once to the other worksheet.
The example I gave you should work except for the following

1. You will need to modify the routine to identify each row.
2. You will need to set up the array as a column by row array so that you
can use Redim Preserve the existing information while you are adding
additional rows in the second dimension.
3. Finally you will need to transpose the array as you post it in the other
sheet.



"Egon" wrote:

> Are you saying I sould just copy and paste on a per line basis between
> the two workbooks and that it would be faster to do so?
>
> Alok wrote:
> > Hi
> > Here is some code.. However you can accomplish this more succintly by just
> > copying and pasting in code.
> >
> > Public Function CopyFromOneSheetToAnother(ByVal sOne$, ByVal sOther$) As
> > Boolean
> >
> > Dim aValues() As Variant
> > Dim i%, lNumRows&
> > Dim wsOne As Worksheet
> > Dim wsOther As Worksheet
> >
> > Set wsOne = ThisWorkbook.Worksheets(sOne)
> > Set wsOther = ThisWorkbook.Worksheets(sOther)
> >
> > lNumRows = wsOne.Cells.SpecialCells(xlCellTypeLastCell).Row
> > ReDim aValues(1 To lNumRows, 1 To 3)
> > For i = 1 To wsOne.Cells.SpecialCells(xlCellTypeLastCell).Row
> > aValues(i, 1) = wsOne.Cells(i, 1).Value
> > aValues(i, 2) = wsOne.Cells(i, 2).Value
> > aValues(i, 3) = wsOne.Cells(i, 3).Value
> > Next i
> >
> > With wsOther
> > With .Range(.Cells(1, 1), .Cells(lNumRows, 3))
> > .Value = aValues
> > End With
> > End With
> >
> > End Function
> >
> >
> > "Egon" wrote:
> >
> > > I need help setting up an array to read data into.
> > >
> > > I would like to read data from 3 columns on one sheet into an array and
> > > then copy that information into another sheet.
> > >
> > > The Array/Matrix will be 3 columns wide, but the number of rows will
> > > need to be dynamic.
> > >
> > > Can anyone help?
> > >
> > > TIA
> > > E
> > >
> > >
>
>