Solved: Copying data onto next empty row, under certain criteria

[Anomandaris]

Hi there,

I'm just trying to copy a bit of info from sheet 2 to sheet 1 in a different format, under certain criteria.

In sheet2 you can see some data in columns C,D, and E
If column C value is 'b' then I need the column D value copied into Column E(sheet1), and i need column E value copied into Column I(sheet1).

If column C value is 's' then column D gets copied to ColumnJ(sheet 1), and Column E gets copied to Column M(sheet1)

A few of the columns are hidden, thats the way the sheet is supposed to be.

And when it gets copied, it should not delete any existing data on sheet1. It should get copied onto an empty row. So if there is already information relating to a 'BUY in row 3, it will copy the 'SELL' info in the next empty row (as i have demonstrated in Sheet 1)

Thanks a bunch guys

[Anomandaris]

just bumping this up, so its visible

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "C" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With Worksheets("Sheet2")

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
NextRow = 3
For i = 2 To LastRow

With .Cells(i, TEST_COLUMN)

If .Value = "b" Then

.Offset(0, 1).Copy Worksheets("Sheet1").Cells(NextRow, "E")
.Offset(0, 2).Copy Worksheets("Sheet1").Cells(NextRow, "I")
ElseIf .Value = "s" Then

.Offset(0, 1).Copy Worksheets("Sheet1").Cells(NextRow, "J")
.Offset(0, 2).Copy Worksheets("Sheet1").Cells(NextRow, "M")
End If
End With

NextRow = NextRow + 1
Next i

End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub
[/vba]

[Anomandaris]

Thanks xld,

it works but I realize now I need a slight adjustment...I need it to start copying info into Row10 on Sheet1. Rows 1-9 on Sheet1 will be blocked.

How do i adjust it?

something like -

.Offset(0, 1).Copy Worksheets("Sheet1").Range("E10").Cells(NextRow, "E")
.Offset(0, 2).Copy Worksheets("Sheet1").Range("I10").Cells(NextRow, "I")

[Bob Phillips]

Set NextRow to 10 at the start.

[Anomandaris]

thanks man, yeah I just figured that out, hey im getting better at understanding this stuff.

thanx a lot man, this stuff is great

[Anomandaris]

oops its not working when I go for a 2nd round of copypaste.
Say i send a set of data to Sheet1 using the macro for 'Buys', then after a while I try to send some 'Sells' from Sheet2 using the macro - what happens is it copies the Sells on the same rows as the existing Buys, its not recognizing the fact that the 'BUY' columns are not empty.
I'm trying to get it into next empty row. any ideas?

thanks

[Anomandaris]

so basically i need something in the code that only copies data into a row if columns E & J are empty

[Bob Phillips]

Do you mean that you want to just append to whatever is there?

[Anomandaris]

yeah, sort of......
for e.g. Say Sheet1 already has 'BUY' data on columns E & I from Row 10 to Row 20, then when I'm using the macro to send 'SELL' data I want it to copy into Columns J & M, but from Row 21 onwards.......so that the same row does not have both BUY and SELL figures.

I may have to use the macro several times to send data from sheet2 to sheet1, without deleting the previously sent(via macro) info.

I hope I didnt confuse you there, let me know if i havent been clear

[Anomandaris]

just bumping this up the list so ppl can see

[Anomandaris]

hey Xld

any idea how to fix this, it seems so close to working.

thanks