Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one sheet to another
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one sheet to another
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" wrote: 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? . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy data from one sheet to another
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy rows from one Data sheet to another sheet based on cell conte | Excel Discussion (Misc queries) | |||
Copy Data from Sheet 1 to Empty Cell in Sheet 2 | Excel Programming | |||
Auto copy cell data from source sheet to another wrkbook sheet | Excel Programming | |||
How can i copy data from a tabbed working sheet to a summary sheet | Excel Discussion (Misc queries) | |||
how to copy a cell with formula from sheet 1 (data is all vertical) into sheet 2 | Excel Worksheet Functions |