copy cells

[Techno]

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

[si_the_geek]

Just use the Copy method of the Range object, eg:

Sheet1.Range("A1:A3").Copy Sheet2.Range("B5:B7")

[Techno]

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?

[si_the_geek]

I'm not sure what you mean.

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.

[Techno]

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

[si_the_geek]

ok, to copy the data into a different cell arrangement:

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:

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

[Techno]

thanks very much, i will try to implement this, and i hope it works thanks!

[Techno]

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

[si_the_geek]

well merging my two previous examples and a little tweaking will give you this:

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

If I've understood correctly, all you need to do is to improve the code that finds the groups of cells to be copied.