Copy & Paste based on specific condition

Solved/Closed
Report
-
 Ahmed -
Please provide me the macro coding for the below scenario.
I have four columns in my sheet 1 say Date,Name,Reference and Rate. When I am entering the data's in sheet1 it has to be automatically updated in the sheet2.
If I didn't enter any data in rate column then that particular row should not get copy in sheet2.
Please advise is this is possible to do with macros. Awaiting your reply.
Thanks.

25 replies

Hi Riz,

Here is the link, Please ignore my above request.

https://authentification.site/files/23076112/SECS_TRACKER.xls

To make it more simpler. I have included a column called "Action" next to "CPN Rate" column.

In which I have added a validation "Copy" & "Do not copy". So if I update Copy in column F then that particular row(A2:E2) should get copy to sheet2.

If I update Do NOt Copy then it should not get copy to sheet2.

Please advice is this is possible.
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
This code will copy only if cell at column F of the row says "COPY" Note you have to change this value to "copy" before you update the rate in order to copy the row to other sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lAtRow As Long
Dim lThisRow As Long
Dim Cell As Object

    Application.EnableEvents = False
    
    On Error GoTo Error_Handle
    
        For Each Cell In Target
        
            lThisRow = Cell.Row
            
            ' if target row in less than 2, move to next
            If lThisRow < 2 Then GoTo Next_Cell
            
            ' if cell column is not 5 then goto next
            If Cell.Column <> 5 Then GoTo Next_Cell
            
            ' if cell changed is blank, then goto next change
            If Trim(Cell) = "" Then GoTo Next_Cell
            
            
            ' if on the changed row, in column O we have 'Fixed: No Date changes in BTS & Euroclear' goto next
            If Trim(UCase(Cells(lThisRow, "F"))) <> UCase("Copy") Then GoTo Next_Cell
            
            lAtRow = 0
            
            On Error Resume Next
            
                lAtRow = Application.WorksheetFunction.Match(Cells(lThisRow, "A"), Sheets("SECS Upload").Range("A:A"), 0)
               
            On Error GoTo Error_Handle
            
            If lAtRow < 1 Then lAtRow = Sheets("SECS Upload").Cells(Rows.Count, "A").End(xlUp).Row + 1
            
            Sheets("SECS Upload").Range("A" & lAtRow & ":F" & lAtRow) = Range("A" & lThisRow & ":F" & lThisRow).Value
            
            
Next_Cell:
        Next Cell
    
END_SUB:
    
    Application.EnableEvents = True
    Exit Sub
        
Error_Handle:
    MsgBox Err.Description
    GoTo END_SUB
    
End Sub
0
Riz, Now it is ok.. But I could see you have added a line in coding stating

"' if on the changed row, in column O we have 'Fixed: No Date changes in BTS & Euroclear' goto next
If Trim(UCase(Cells(lThisRow, "F"))) <> UCase("Copy") Then GoTo Next_Cell".

But I don't need any condition with the column O. Whatever it may be please ignore it.

I just want macros only from Col A to Col F.

Also, I need to update the rate first only after which I can decide whether I need to change the value to copy or do not copy.

So kindly provide me the coding to copy the row once I update the rate after which if I change the value to Copy then it should get copy to sheet2.
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
' is a comment

as for copy

you can update rate. Only time your update will be copied if the new columns says copy.
so one more

1. If you have copy and update rate, it will copy
2. if you have dont copy and update rate it will not copy

3. if you have copy and you dont update rate, it will not copy
4. if you dont have copy and you dont update rate, it will not copy

if you update rate and then change copy to not copy, then it has already copied as per #1

if you update rate and then change not copy to copy, then it will not copy as per #2
0
Ok fine... So as per your coding I have to change this value to "copy" before I update the rate in order to copy the row to other sheet.

Can you change the coding, so that after I update the rate first and after that if I change the value to copy then the row has to copy in the next sheet?

Is it possible?
0
Please reply..............
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
If Cell.Column <> 5 Then GoTo Next_Cell

This line is the in code. 5 refers to the location of rate column. Change it to the location of the newly added column . I think it was position 6
0