Conditional Copy/paste data into another sheet

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

PHP 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

But when i get new data on Sheet2

When you do this, does the new data overwrite existing data or is it added to the bottom? If the latter, your code appears to work as you'd like so is that not an option?

hey Stephen thanks for looking at this...
I know what ur suggesting but that would pose a problem, as the data in sheet 2 gets adjusted (with some calculations before it gets sent to Sheet1), so when new data comes along, the older data would get calculated twice and distorted....

thats why i was hoping for another solution, any ideas?

Try changing the line setting NextRow to 3 to this:

Please Login or Register  to view this content.

Thanks Stephen, that works well for that sheet.
But on my actual sheet, things are slightly different.

On Sheet1 I need to start the Data input/Copypaste from Row 10
And the hidden columns are posing a problem, because there maybe data in those. Its a client worksheet, and those columns are locked, sometimes it may have data in there.

I tried to adjust it by changing the Range

Range("E10:E1000, I10:I1000, J10:J1000, M10:M1000")

but that didnt work, any suggestions on how to adjust it?

many thanks

It's better if you can upload an attachment which actually reflects what you're doing. A bit messy but try this:

Please Login or Register  to view this content.