Copy data from one sheet to another

[Slim Slender]

Here is my procedure. It quite doesn't work.


Public Sub CopyRecordsfromDatatoDatabase()

Dim myArray(7)
Dim myColumn As Integer, myRow As Single
Dim i As Integer

myRow = 2
i = 1

Do
For myColumn = 6 To 26

Worksheets("data").Activate
With ActiveSheet
.Cells(myRow, 5) = myArray(1)
.Cells(myRow, 3) = myArray(2)
.Cells(myRow, 2) = myArray(3)
.Cells(1, myColumn) = myArray(4)
.Cells(myRow, myColumn) = myArray(5)
.Cells(myRow + 1, myColumn) = myArray(6)
.Cells(myRow + 2, myColumn) = myArray(7)
End With

Worksheets("Database").Activate
With ActiveSheet
i = i + 1
.Range(.Cells(i, 1), .Cells(i, 7)) = myArray
End With

Next myColumn
myRow = myRow + 3
Worksheets("data").Activate

Loop Until Cells(myRow, 1).Value <> ""

End Sub

It cuts all the data from the first ("data") sheet which is okay
except for one item I need to be copied repeatedly (Cells(1,
myColumn)).
It does not copy/write to the second sheet at all because the array is
not being populated, but it does overwrite the appropriate cells on
the second ("Database") sheet with nothing.
Once I get the array to populate, I would need to empty or reset it
after each loop, wouldn't I?

 

[Matthew Herbert]

Slim,

I'm not sure how "it cuts all the data from the first ("data") sheet" is
happening, but you are right about the array not being populated. This is
because your assignment is reversed.

First off, an array is zero-based by default, unless you specify otherwise.
So, your "Dim myArray(7)" can hold 8 elements, e.g. myArray(0), myArray(1),
etc. The complier will automatically assign values to the variables you
dimension (at least for the most part). Thus, your array starts out with
empty values in each indexed location. Therefore, without "loading" the
array, ".Cells(myRow, 5) = myArray(1)" means that ".Cells(myRow, 5)" will
take an empty value (or the empty value in "myArray(1)". (The code on the
left is assigned to the code on the right). However, "myArray(1) =
..Cells(myRow, 5)" will load the value of ".Cells(myRow, 5)" into
"myArray(1)", or the 1-index location of the array. When "unloading" the
array, you can do it one-by-one, or you can do it via sizing the output range
and then setting the output range equal to the array (or you can even
Transpose the array if necessary).

If you have one cell, say ".Cells(i, 1) = myArray", it's kind of hard for
the 8 elements in myArray to be output into one cell. (Also, if the range is
larger than the array size, you'll get a fun little output). Therefore, the
compiler will do it's best to fill the one cell, which should be filled by
the first index value in myArray.

Two alternative, sample approaches are as follows:

One:
For intCnt = LBound(myArray) To UBound(myArray)
ActiveSheet.Cells(intCnt, 1).Value = myArray(intCnt)
Next intCnt

Two:
Correctly size the range to fit the array size and the output rows (i.e. "i,
7" was changed to "i, 8" to account for the size of the zero-based array
With ActiveSheet
i = i + 1
.Range(.Cells(i, 1), .Cells(i, 8)) = myArray
End With

Using the For Next loop can help you "reset" or assign "dummy" values to the
array. The other approach is to do a direct worksheet-to-worksheet
assignment. For example, see the following (which is untested):

Dim wksData As Worksheet
Dim wksDB As Worksheet
Dim lngRow As Long

Set wksData = Worksheets("Data")
Set wksDB = Worksheets("Database")

For lngRow = 1 To 10
wksDB.Cells(lngRow, "C").Value = wksData.Cells(lngRow, "A").Value
Next lngRow

Start with this information and see if this puts you on the right path.

Best,

Matthew Herbert

 

[Slim Slender]

Thanks for the help, Matthew. Turning the statements around was what
it needed.
The size of the array was not a problem because I have Option Base 1
at the top.