Previous Topic Next Topic

Insert row data on Sheet2 based on sheet1 cell value(text)

[Closed]
Report
Posts
1
Registration date
Saturday May 10, 2014
Status
Member
Last seen
May 10, 2014
 -
Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
 -
Hello,

i have two sheets i.e., sheet1,sheet2
in Sheet1 row fields like
A B C D E in that D field value like RC
based RC value insert whole RC row data can be insert in Sheet2

2 replies

Answer 1 / 2
Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
 482
Hi Anilkarthik,

If I understand you correctly, you want to copy row from sheet1 to sheet2 whenever "RC" is entered in Column D.

To do so, right-click sheet1's tab and select view code. Paste the code below in the big white field: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub
If Target.Value = "RC" Then
    Target.EntireRow.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
End Sub


Best regards,
Trowa
1
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Report

Hello, I have tried to apply this Macro Code for my worksheet but I get an error in the below line,

If Target.Value = "RC" Then

I am not able to troubleshoot how to fix this. Any help would be greatly appreciated!
Report

ok so how do I get the code above to become active? I copied and paste the code in the big white box but when I press enter, it just takes me to the next line to add more...
there is a drop down box on the upper right hand side with options such as activate, change, calculate, deactivate, etc

thanks in advance for your assistance!!!
Report
Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
 482
Hi CV,

The code will run automatically. So after pasting the code go back to Excel and enter the value "RC" anywhere in column D and the row will be copied to sheet 2, if you haven't altered the code.

Best regards,
Trowa
Answer 2 / 2
Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
 482
Hi Lack,

My guess would be that you selected more then one cell when changing column D. This could happen when clear data from multiple cells.

To prevent this you can add the following code line between the 2nd and the 3rd code line:
If Target.Cells.Count > 1 Then Exit Sub

Does that help?

Best regards,
Trowa
Ask a question