Copy & Paste based on specific condition [Solved/Closed]

Saky - May 31, 2010 at 05:13 AM - Latest reply:  Ahmed
- Apr 29, 2015 at 07:14 PM
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.
See more 

53 replies

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - May 31, 2010 at 06:07 AM
0
Thank you
Look at this
http://ccm.net/forum/affich-348420-writing-a-macro

it needs a minor tweak for your case
Thanks for your response riz. But unfortunately I was not able to implement this macro coding to my excel as it does not work properly.

Hence kindly guide me and provide me the exact macro coding for my case.

I have five columns say S.No,date,Name,Reference number and rate in both sheet1 and sheet2.

Now my requirement is, I need a macro to copy the details form sheet1 to sheet2 only if all the five columns get updated. if I didnt update rate in the rate column for a particular row then that particular row should not be copied to sheet2. Hope you get my point!!

Kindly help me to sort it out.
Thanks for your response riz. But unfortunately I was not able to implement this macro coding to my excel as it does not work properly.

Hence kindly guide me and provide me the exact macro coding for my case.

I have five columns say S.No,date,Name,Reference number and rate in both sheet1 and sheet2.

Now my requirement is, I need a macro to copy the details form sheet1 to sheet2 only if all the five columns get updated. if I didnt update rate in the rate column for a particular row then that particular row should not be copied to sheet2. Hope you get my point!!

Kindly help me to sort it out.1
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - May 31, 2010 at 12:49 PM
Could you post a sample file along with your RE-WORKED macro on some shared site like http://www.speedyshare.com/ , http://wikisend.com/ , etc and post back here the link to allow better understanding of how it is now and how you foresee.
0
Thank you
http://www.speedyshare.com/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.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 1, 2010 at 08:15 AM
But did you not say "But unfortunately I was not able to implement this macro coding to my excel as it does not work properly. " ??

So what exactly did you do ?
0
Thank you
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.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 1, 2010 at 04:04 PM
No book at the link . Could you re-post
0
Thank you
http://www.speedyshare.com/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.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 2, 2010 at 10:57 AM
Looking at your data, you said that copy a row only if all columns are updated. That would be very hard to do. If one column is updated, that would be possible.
okay..So if only the rate column is updated then that particular row can be copied to sheet2 using macros? Is that possible?
0
Thank you
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
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 2, 2010 at 12:44 PM
The instructions are pretty much same as one in the thread I pointed you too. This is the tweaked code from same thread

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 ((lThisRow > 1) And (Cell.Column = 5) And (UCase(Cell) <> "")) Then
                            
                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
                
            End If
        
        Next Cell
    
END_SUB:
    
    Application.EnableEvents = True
    Exit Sub
        
Error_Handle:
    MsgBox Err.Description
    GoTo END_SUB
    
End Sub
Ahmed > rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Apr 29, 2015 at 07:14 PM
Hey Riz

May I ask for your help with a similar code ?
i have a sheet consists of 18 Column but i'm looking only for one of them have on of 17 fixed entries
so the code should copy the row of every entry and paste it in the Sheet that match the name of this specific cell
Can you send me your answer over this mail: ***@***
0
Thank you
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.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 3, 2010 at 10:38 AM
if you correct the rate, the row gets updated. It looks for the serial number. If the serial number is on sheet2, then data from sheet 1 is used to update the sheet 2
if the serial number is not found, then data from sheet 1 is added to sheet2
0
Thank you
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.

http://www.speedyshare.com/files/22988970/SECS_TRACKER.xls

Is this is possible?

Please advice.
0
Thank you
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.


http://www.speedyshare.com/files/22988970/SECS_TRACKER.xls


Is that possible. Please advice. Thanks for your help!!!
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 16, 2010 at 12:31 PM
Is this different from http://ccm.net/forum/affich-398061-copy ?
aquarelle 7183 Posts Saturday April 7, 2007Registration dateModeratorStatus May 25, 2018 Last seen - Jun 16, 2010 at 02:03 PM
Hello,

I deleted all the other same threads ! For the same question continue on the initial thread. Don't create a new one each time.
And if you want to bump up your thread, just add a message with a "up" in your initial discussion but not all minutes, be patient ... We are all volunteers and we have a job and a real life too ;)

Best regards
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 16, 2010 at 06:25 PM
Life ? whats that aquarelle ? :P

In Saky defense, there was a glitch in the system. It looked liked messages were not being posted then. I think he thought same too and kept on adding messages
aquarelle 7183 Posts Saturday April 7, 2007Registration dateModeratorStatus May 25, 2018 Last seen - Jun 17, 2010 at 06:14 AM
:D, thanks for these informations.
0
Thank you
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 7183 Posts Saturday April 7, 2007Registration dateModeratorStatus May 25, 2018 Last seen - Jun 17, 2010 at 06:08 AM
0
Thank you
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."
0
Thank you
Sorry I dint get you aquarelle..

Can I get an answer for the above please...I need your help..
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 17, 2010 at 10:07 AM
Saky
The file that you have up there. The VBA is password protected. Either put up a file without one or what is the password ?
Password is : dau123...Sorry Riz, I forgot that.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 17, 2010 at 10:32 AM
does that file even work ? the macro is in the wrong place. ?
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 17, 2010 at 10:41 AM
0
Thank you
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
0
Thank you
No riz, It is not working..Still the rows get copied to sheet2..
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 17, 2010 at 01:43 PM
Upload a file with data and macro. Then tell me STEP by STEP to replicate the error you get
0
Thank you
Here is the link..

http://www.speedyshare.com/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.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 18, 2010 at 05:27 AM
Lets see how good you are at spotting things


Fixed: No Date chenges in BTS & Euroclear
Fixed: No Date changes in BTS & Euroclear

The first line is from your data
the 2nd line is from code

Spot the difference.:P
0
Thank you
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.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 18, 2010 at 07:12 AM
Check in which columns are you putting the comments,. It not O it is M
0
Thank you
Riz,

Please check the link.

http://www.speedyshare.com/files/23019744/SECS_TRACKER.xls

I have saved with the codings. please check and tell where I am going wrong?

Thanks.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 18, 2010 at 11:44 AM
what to check, there is no data. Please put up a file with data and then give me a step by step. how to replicate what you are doing. Tell me cell address, text to address, order in which to go, sheet to go etc. and remove the wrongly placed macro too
0
Thank you
http://www.speedyshare.com/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.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 18, 2010 at 02:36 PM
This is what I did

1. open the workbook

2. went to tab SECS Data

3. saw the name in cell C2 (Sakthi) and saw the rate in E2 (5.1) and saw the comments in cell O2 (Fixed: No Date changes in BTS & Euroclear)

4. saw the name in cell C3 (Ajeesh) and saw the rate in E3 (2.1) and saw the comments in cell O3 (Rate updated)

5. went to SECS Upload tab

6. saw the name in cell C2 (Sakthi) and saw the rate in E2 (5.1)

7. saw the name in cell C3 (Ajeesh) and saw the rate in E3 (2.1)

8. went to tab SECS Data and change E2 to 100 and E3 to 1000

9. went to SECS Upload tab

10 saw the rate values in E2 and E3. E2 remained 5.1 and E3 was changed to 1000

This is my step by step test. and it works as I think it should work.

NOW, if you dont think this is right or you did not get same result . PLEASE give me a STEP BY STEP as I just gave you.
0
Thank you
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..
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 21, 2010 at 05:24 AM
Give me a step by step of how each sheet would look as I did.
0
Thank you
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.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 21, 2010 at 09:36 AM
OK if get this right

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".

Initially sheet 1 was showing ""Fixed: No Date changes in BTS & Euroclear".
Then you changed that value to "rate updated"

so you dont want to copy the row, if that row held "Fixed: No Date changes in BTS & Euroclear" earlier, no matter what you changed including this "Fixed: No Date changes in BTS & Euroclear"

Question 1: is that correct. Then correct me, else answer the 2nd question


Question 2: is this was correct then
The problem in this case is that there is no easy way of know what column O held before you made all the changes. The more robust answer would be you add one more column or change the font color or some thing, that say that do not copy this row, no matter what is done on this row, Is that possible
0
Thank you
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?
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 21, 2010 at 10:43 AM
Can you add a new file with macro and the newly added column, so one can see how the file design
1 2 Next

copy & Paste based on specific condition - page 2