copy & Paste based on specific condition Solved/Closed

Question

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.

rizvisa1 · Answer

Look at this
https://ccm.net/forum/affich-348420-writing-a-macro

it needs a minor tweak for your case

Saky · Answer

https://authentification.site/files/22736746/Copy_of_SECS_Tracker_6.xls

Here is the link...I have not written any macros for the attached excel..Kindly provide me the macro coding to copy only the rows in which I have updated the rate in CPN rate column to sheet2.

Thanks in Advance.

Saky · Answer

Riz,

I am not a expert in macros..i am just a beginner in macros.  So I tried with the coding and I am not sure which part I need to change in the codings...So it would be great if you could provide me a fresh coding which suits to my excel.

Thanks.

Saky · Answer

https://authentification.site/files/22753043/Copy_of_SECS_Tracker.xls
http://wikisend.com/download/466678/Copy of SECS Tracker.xls

I have posted the excel in both the links..

Thanks for your help in advance.

Saky · Answer

Fine..IF I update any details in the rate column then that particular row alone can be copied to sheet2 using macros?Is that possible?

Thanks

Saky · Answer

Riz,

Thanks a million this code works perfectly.. 

If I fill the details in Rate column then the entire row is automatically gets copied to sheet2.

But if I incorrectly enter details in rate column which I was not supposed to do and so if I delete the details in that column. In such case the row which is already copied to sheet2 can be deleted once I remove details in row column in sheet1?

Is it possible?

Thanks.

Saky · Answer

Hi Riz,

The above coding which you gave for copying and pasting row to sheet2 if the rate column is updated in sheet1 works really great!!

Adding to that, I need another specific condition added to the above scenario.

In the attached excel I have column "O"(Remarks - If any), I have provided few validation list in this column. If this column reads as "Fixed: No Date changes in BTS & Euroclear". Then that particular row should not get copied to the sheet2 even if the rate is updated.

https://authentification.site/files/22988970/SECS_TRACKER.xls

Is this is possible?

Please advice.

Saky · Answer

Hi Riz,

The above coding for copying rows from sheet1 and pasting it in sheet2 if the rate column is updated works really great!!!

I need to add another condition in copying cells to sheet2.

I have column "O"(Remarks - If any) in my excel. In which I have added few validations.

So if the column "O" reads "Fixed: No Date changes in BTS & Euroclear" then that particular row should not get copied to sheet2 even if the rate is updated.


https://authentification.site/files/22988970/SECS_TRACKER.xls


Is that possible. Please advice. Thanks for your help!!!

Saky · Answer

Sorry for the confusion caused guys!!! I know you people are helping us greatly..

But as Riz said...there was a problem with the system that is the reason why I posted the message thrice and not in the intention to trouble you people..

Anyways ance again I apologise for the confusion caused..

Can you kindly reply me for my above question.

Thanks...

aquarelle · Answer

Hi, 

I hope that I was not too severe and I apologise because I didn't know that there was a system problem when you tried to post.  
But I wanted to explain why your messages have been deleted. 

Have a good day! 

PS : Saky, if you want some help, put an example without password ;)

"Pour trouver une solution à ses problèmes, il faut s'en donner la peine."

Saky · Answer

Sorry I dint get you aquarelle..

Can I get an answer for the above please...I need your help..

rizvisa1 · Answer

Ok try this.

It needs to go to the sheet where the change of rate is happening


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, "O"))) = UCase("Fixed: No Date changes in BTS & Euroclear") 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

Saky · Answer

No riz, It is not working..Still the rows get copied to sheet2..

Saky · Answer

Here is the link..

https://authentification.site/files/23014487/Copy_of_SECS_Tracker.xls

I am not getting any error. But when I enter rate it automatically gets copied to sheet2 even I enter "Fixed: No Date changes in BTS & Euroclear" in column O.

Saky · Answer

Riz,

I found that already and I have altered and added new validation in the excel. But still it is not working... The data which is reflecting in column O is a old one...Please ignore it..Just check the validation it will be correct.

Saky · Answer

Riz,

Please check the link.

https://authentification.site/files/23019744/SECS_TRACKER.xls

I have saved with the codings. please check and tell where I am going wrong?

Thanks.

Saky · Answer

https://authentification.site/files/23021364/SECS_TRACKER.xls

See this excel. when I enter "Fixed: No Date changes in BTS & Euroclear" it is getting copied to sheet2. I have pasted the coding which you have given above...pls check and advise.

Saky · Answer

Thanks for your detailed explanation Riz, But I am not expecting this.

If I Fill O2 as "Fixed: No Date changes in BTS & Euroclear" then that particular row should not get copied to sheet 2(SECS Upload ) even though if I fill the rate in that column.

I hope this clarifies you.

I dont want the rate to get amended if I change col O2 as Fixed: No Date changes in BTS & Euroclear.

If I fill col O2 as Fixed: No Date changes in BTS & Euroclear, then I dont want that row to get copied to sheet2..

Saky · Answer

Step 1: Go to sheet1, In first row update S.No, Value date, Requestor name,ISIN/CUSIP, CPN Rate column and in Column O select "Fixed: No Date changes in BTS & Euroclear".

Step2: Again Go to sheet1, In row 2 update S.No, Value date, Requestor name,ISIN/CUSIP, CPN Rate column and in Column O select "rate updated".

In first Step I have updated all the details and in col O I have selected as "Fixed: No Date changes in BTS & Euroclear". So that row should not get copied to sheet2.

In second step I have updated all the details and in col O I have selected as "Rate updated", so I want that row to get copy in sheet2.

So in sheet2 I need only the second row in which I have updated Col O as "Rate updated".

So the condition what I require is, if I select Col O as Fixed: No Date changes in BTS & Euroclear, then it should not get copy to sheet2.

I hope this helps you to understand.

Saky · Answer

Yes you are right!! I dont want to copy the row, if that row held "Fixed: No Date changes in BTS & Euroclear" .

As you said I can add one more column at the last and update Do not copy, so that the particular row does not get copy to sheet2.

Can you provide me a coding for this?

Saky · Answer

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.

rizvisa1 · Answer

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

Saky · Answer

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.

Saky · Answer

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?

Saky · Answer

Please reply..............

Copy & Paste based on specific condition

25 responses

Similar discussions

Subscribe To Our Newsletter!