CODEWITHSUNDEEP
excelvba
DrBeton
DrBeton

Reputation: 3

Copy data from one sheet and paste to another (under some conditions)

My macro copies data from several sheets (the particular one is called SS) and pastes it to another one (MainSheet):

Dim lastrow As Long
'(...)

Lastrowss = Sheets (“SS”).Range(“A1”).End(xlDown).Row
'(...)

For i = 2 To lastrows
'    (...)

    Sheets("MainSheet").Cells(j, 7).Value = Sheets("SS").Cells(i, 7).Value 'Rec Amount
    Sheets("MainSheet").Cells(j, 9).Value = Sheets("SS").Cells(i, 9).Value * -1  'Paid Amount

The macro works only for the sells. Buys are reflected in the opposite way.

The macro should copy the data from column 7 and 9 and paste it to columns 9 and 7 (not 7 and 9) only if there is "buy" in column D. If there is a "sell" the data should remain the same.

In column D you do not always get just “sell” or “buy”. You could also see “sell to cover” etc. But the logic remains the same.

Upvotes: 0

Views: 102

Answers (2)

DrBeton
DrBeton

Reputation: 3

Thanks all for help. I have slightly updated the code:

If Left(Sheets("SS").Cells(i, 4).Value, 3) <> "Buy" Then
    Sheets("MainSheet").Cells(j, 7).Value = Sheets("SS").Cells(i, 7).Value 'Rec Amount
    Sheets("MainSheet").Cells(j, 9).Value = Sheets("SS").Cells(i, 9).Value * -1  'Paid Amount
Else
    Sheets("MainSheet").Cells(j, 7).Value = Sheets("SS").Cells(i, 9).Value 'Rec Amount
    Sheets("MainSheet").Cells(j, 9).Value = Sheets("SS").Cells(i, 7).Value * -1  'Paid Amount

End If

Seems to work now.

Upvotes: 0

A.S.H
A.S.H

Reputation: 29352

If Sheets("SS").Cells(i, 4).Value <> "buy" Then ' <-- add this test
    Sheets("MainSheet").Cells(j, 7).Value = Sheets("SS").Cells(i, 7).Value 'Rec Amount
    Sheets("MainSheet").Cells(j, 9).Value = Sheets("SS").Cells(i, 9).Value * -1  'Paid Amount
Else ' <-- here we inverse the two columns
    Sheets("MainSheet").Cells(j, 7).Value = Sheets("SS").Cells(i, 9).Value 'Rec Amount
    Sheets("MainSheet").Cells(j, 9).Value = Sheets("SS").Cells(i, 7).Value * -1  'Paid Amount

End If

Upvotes: 2

Related Questions