Macro to copy cells in sh 2 triggered by an entry in one of them and pasted in sh1 into a row with same log no.

[Raceman]

Hello: can someone help me with VBA Code to do the following:

On Sheet 2, after an entry is made in Cell P an event is triggered that will copy cells P and Q (in the same row) only if Cell C in that row = "Task Summary" (note on sheet 2 there is a log no. in cell A)

then on Sheet 1 this info will be pasted into cells Y and Z (and the row with the same Log No.as on Sheet 2) .

Log No., which is a three digit no. starting with 001, is in Column A on both Sheet 1 and 2.

Not sure where to even start on this one​

 

[Raceman]

Ok I'll think about it again , make sure I'm not doing something wrong, and get back to you next week

 

[Raceman]

OK JLGWHiz here is what I did to test your code. I opened a brand new workbook with Sheet 1 and Sheet 2 (Sheet 1 in this example corresponds to my original sheet called "MAD_CAT"). Then, on Sheet 1, in cells A3 - A5 I entered 001, 002, and 003, respectively.

On Sheet 2 I entered 001 in A3 and 002 in A6. I typed "Task Summary" in C3 and C6. I copied your code into SHeet 2 (switching the sheet reference from MAD_CAT to Sheet 1). On Sh2 I then tested all of the auto dates from D4 -P4 and D7 - P7.

Then I added enties to Cells P3 and P6 (on Sh2) to test the transfer of info to Sh1 (because these are the rows that have "Task Summary"). The auto dates worked and displayed in Q3 and Q6 on Sh2, but when I went into Sh1 I discovered the tranfer of info did not work completely. It only worked on line 3 cell Y3 and Z3.

So as far as I can tell .....this confirms the problem and hopefully gives you a better understanding of what I am trying to accomplish. Perhaps I led you astray with an earlier posting.

 

[Raceman]

OK I'm getting closer I think: if I change this part of your code from:

Set fVal = Sheets("Sheet1").Range("A:A").Find(Range("A" & Target.Row).Value, LookIn:=xlValues)

To:

Set fVal = Sheets("Sheet2").Range("A:A").Find(Range("A" & Target.Row).Value, LookIn:=xlValues)

then I get a consistant copy/ paste on to Sheet 1 Y and Z , But not on the row with the equal Log No. which is in column A on both sheets. Is the code not looking at column A on Sheet 1?

 

[JLGWhiz]

Let's simplify this. What do you expect to see on the row in sheet 1 which has the same ID number as the row in sheet 2 after the code runs? Bearing in mind that your origtinal post only specified pasting in Columns Y and Z.

 

[Raceman]

What do I expect to see in Sheet 1? In Sh1 Y I expect to see what was entered into Sh2 P, and in Sh1 Z I expect to see the date that appeared in Sh2 Q.

 

[JLGWhiz]

Ok, I think I was testing the wrong sheet for "Task Summary". See if this works.

Set t = Target
Set dd = Range("$D$3:$D$5000")
Set ff = Range("$F$3:$F$5000")
Set hh = Range("$H$3:$H$5000")
Set jj = Range("$J$3:$J$5000")
Set ll = Range("$L$3:$L$5000")
Set nn = Range("$N$3:$N$5000")
Set pp = Range("$P$3:$P$5000")
If Target.Count > 1 Then Exit Sub
 If (Intersect(t, dd) Is Nothing) And (Intersect(t, ff) Is Nothing) And (Intersect(t, dd) Is Nothing) And (Intersect(t, hh) Is Nothing)  `And (Intersect(t, jj) Is Nothing) And (Intersect(t, ll) Is Nothing) And (Intersect(t, nn) Is Nothing) And (Intersect(t, pp) Is Nothing)  Then Exit Sub
Application.EnableEvents = False
t.Offset(0, 1).Value = Date
Application.EnableEvents = True
'This code automatically moves data from cells P and Q to Sheet 1 but only if Sh2 line says Task Summary
Dim fVal As Range
    If Not Intersect(Target, Range("P:P")) Is Nothing Then
        Set fVal = Sheets("MAD_CAT").Range("A:A").Find(Range("A" & Target.Row).Value, LookIn:=xlValues)
            If Not fVal Is Nothing And [COLOR=#b22222]Range("C" & Target.Row)[/COLOR] = "Task Summary" Then
               Target.Resize(1, 2).Copy Sheets("MAD_CAT").Range("Y" & fVal.Row)
            End If
    End If
End Sub

See change in red.

 

[Raceman]

Fantastic! Yes that did it and resolved my issue. Thank you so much!

 

[JLGWhiz]

Sometimes even a blind hog can root out an acorn!
Regards, JLG