hi there
in vba - how do i copy the cells from one sheet to another sheet?
these cells are in groups, group contains 3 rows, i wanna copy each group into another sheet
thanks :)
Printable View
hi there
in vba - how do i copy the cells from one sheet to another sheet?
these cells are in groups, group contains 3 rows, i wanna copy each group into another sheet
thanks :)
Just use the Copy method of the Range object, eg:
Sheet1.Range("A1:A3").Copy Sheet2.Range("B5:B7")
wow many thanks for the speedy response! just what i need!
i understand that, however, hmm hard to explain
this sheet, has many groups of rows.
each group has 3 values
for every group in this sheet, i want to copy the values to another sheet but in one line. so each group values are put on a new line = see where im going?
we wouldnt know, well we kinda do, where to begin or where to stop looking for the cells with entries.
he has given me starting points,
like:
A8 + 3 rows
B9 + ""
C9 + ""
..
..
F10 + 3 rows
..
..
? know what i mean?
I'm not sure what you mean. :ehh:
Do you mean you want the data formatted differently in the target location?
Can you find the source cells?
By the way, the best method for finding out what code you need (assuming you can do it in Excel manually) is to record a macro of the actions, then Excel writes the VB code for you.
I know in this situation it probably wont help that much, but you can find part of the solution. ;)
:)
yes, in the destination - the data is formatted differently
how should i go about doing this?
how do u place the copied cell values into the destination cells?
(syntax)
how do i make a loop which will detect if there is text in the next 3 cells/rows, if there is it will continue copying, otherwise if the next 3 cells have no text, it will stop copying
:) ty
ok, to copy the data into a different cell arrangement:
VB Code:
Dim tmpArray 'copy values to an array tmpArray = Sheet1.Range("A1:A3").FormulaArray 'put them back out again Sheet2.Range("B3") = tmpArray(0) Sheet2.Range("C4") = tmpArray(1) Sheet2.Range("D5") = tmpArray(2)
As for detecting values, just loop until cells are empty.
eg:
VB Code:
Dim iRowNum as Integer Dim iColNum as Integer Dim bGotNextCell as Boolean iRowNum = 1 iColNum = 1 Do '(copy code here) bGotNextCell = False 'basic example - only checks cell to the right or below '(might be better to put this into a For loop) If Sheet1.Cells(iRowNum + 1, iColNum) <> "" Then iRowNum = iRowNum + 1 bGotNextCell = True ElseIf Sheet1.Cells(iRowNum, iColNum + 1) <> "" Then iColNum = iColNum + 1 bGotNextCell = True End If Loop While bGotNextCell
thanks very much, i will try to implement this, and i hope it works :) thanks!
im still annoyed and stressed at this, dont even know where to begin, sitting here for 3 hours looking at the screen dunno what to type. aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
so i spoke to the guy again. and he says:
for every group, each group consists of 3 rows, each of these 3 rows must be on 1 row with the project header, which is on row 1 of each group.
so how do i go about do a loop which will take each cell value, and copy it to one row in different columns, until the counter has hit 3, which will move on to the next group? aaaaaaaaaaaaaaaaaaaaaaa
well merging my two previous examples and a little tweaking will give you this:
If I've understood correctly, all you need to do is to improve the code that finds the groups of cells to be copied.VB Code:
Dim iRowNum as Integer Dim iColNum as Integer Dim iOutputRow as Integer Dim bGotNextCell as Boolean iRowNum = 2 iColNum = 1 iOutputRow = 1 Do 'find next cell group bGotNextCell = False 'basic example - only checks cell to the right or below '(might be better to put this into a For loop) If Sheet1.Cells(iRowNum + 1, iColNum) <> "" Then iRowNum = iRowNum + 1 bGotNextCell = True ElseIf Sheet1.Cells(iRowNum, iColNum + 1) <> "" Then iColNum = iColNum + 1 bGotNextCell = True End If 'copy to output sheet If bGotNextCell Then Sheet2.Cells(iOutputRow, 1) = Sheet1.Cells(1, iColNum) Sheet2.Cells(iOutputRow, 2) = Sheet1.Cells(iRowNum, iColNum) Sheet2.Cells(iOutputRow, 3) = Sheet1.Cells(iRowNum + 1, iColNum) Sheet2.Cells(iOutputRow, 4) = Sheet1.Cells(iRowNum + 2, iColNum) iOutputRow = iOutputRow + 1 End If Loop While bGotNextCell