Conditional Copy/paste data into another sheet

[Pasha81]

Hi guys,

I've got this code, its almost complete needs just one little addition which i cant figure out.


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)

To do this I came up with the following code

 Sub CopyData() 
    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 

now this does the job the first time, you can see the workbook. But when i get new data on Sheet2 and try to run the macro, it copies over the existing data in Sheet1. It pastes 'SELL' data in the same row as 'BUY' data on Sheet 1 , which I dont want.

If on any row is data on Columns E and I, then the macro should paste the 'SELL' data on the next row in columns J and M.

any thoughts how this may be done?
thanks