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]

Sorry, here is the code I am working with (the previous code I had made a change to try to fix it):

'This will automatically provide dates where appropriate
Private Sub Worksheet_Change(ByVal Target As Range)
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 Range("C" & fVal.Row) = "Task Summary" Then
Target.Resize(1, 2).Copy Sheets("MAD_CAT").Range("Y" & fVal.Row)
End If
End If
End Sub

 

[Raceman]

I tested this again JLGWhiz and the only row that works is row 3. When I input data in the active sheet row 3 column K I get the desired result on the MAD_CAT sheet row 3 columns Y and Z, but that's it. The Macro doesn't work on any other rows. I'm puzzled, can you think of any reason why this is happening?

 

[JLGWhiz]

The code is looking for a value in the intersect of the target cell and columns D, F, H, J, L and P. If it finds no value in those columns, it exits the procedure. So for the portion of the procedure to work that enters the date, the target must be in one of those columns. The second part of the procedure will execute only if the target is in column p and will only copy and paste if it finds a match in column A values between the two sheets. The code does not loop, it only executes one time per change on the worksheet. When I tested the code you are now using, it appeared to work properly.

 

[Raceman]

In this discussion I'm talking only about the columns on the active sheet of D thru Q.

I just tested it again - the first row with data is 3 (Rows 1 and 2 are headers). I filled Row 3 out as as "task summary" line. And there is no data in columns D thru O on a task summary line, there is only data in P and Q (where Q is an auto date). For Rows 4 thru 8 I put data into D thru Q.

Again this scenario works only for row 3 with or without data in D thru O. None of the other rows function this same way.


Note: The task Summary line is essentially filled out in P and Q, when I see that all of the "sub-tasks" that are under it (like rows 4-8 in my example) are complete.


I hope this details what I am seeing well enough, I guess I don't mind if the code doesn't need an entry in columns D, F, H, J, L and P (as you said) before it performs its function....it only needs to perform when P and Q are filled out on a row where C = "Task Summary".

Thanks for your help.

 

[JLGWhiz]

In this discussion I'm talking only about the columns on the active sheet of D thru Q.

I just tested it again - the first row with data is 3 (Rows 1 and 2 are headers). I filled Row 3 out as as "task summary" line. And there is no data in columns D thru O on a task summary line, there is only data in P and Q (where Q is an auto date). For Rows 4 thru 8 I put data into D thru Q.

Again this scenario works only for row 3 with or without data in D thru O. None of the other rows function this same way.


Note: The task Summary line is essentially filled out in P and Q, when I see that all of the "sub-tasks" that are under it (like rows 4-8 in my example) are complete.


I hope this details what I am seeing well enough, I guess I don't mind if the code doesn't need an entry in columns D, F, H, J, L and P (as you said) before it performs its function....it only needs to perform when P and Q are filled out on a row where C = "Task Summary".

Thanks for your help.

I think I found the culprit.
Change this:

If Not fVal Is Nothing And Range("C" & fVal.Row) = "Task Summary" Then

To this:

If Not fVal Is Nothing And Sheets("MAD_CAT").Range("C" & fVal.Row) = "Task Summary" Then

 

[Raceman]

When I try to input into P with a task summary or without, I get Run time error '9' Script out of range and it highlights the code we just changed.

 

[JLGWhiz]

Did you type the change or copy it into your code window? In any case check to make sure the Sheet name is spelled correctly, including upper and lowere case. That would be the only thing on that line that would cause the error.

 

[JLGWhiz]

I tested this code. If I make entries on sheet 1 in columns D, F, H, J, L, Or N, it simply puts a date in the column to the right. But if I make an entry into any cell in column P below row 2, It executes part two of the code and not only enters the date on sheet one, but also checks sheet 2 for "Task Summary" and if found, copys columns P And Q from sheet 1.

Private Sub Worksheet_Change(ByVal Target As Range)
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 Sheets("MAD_CAT").Range("C" & fVal.Row) = "Task Summary" Then
               Target.Resize(1, 2).Copy Sheets("MAD_CAT").Range("Y" & fVal.Row)
            End If
    End If
End Sub

 

[Raceman]

Thanks for sticking with me on this, but I see no improvement on my end. It reacts the same way as I have described....works for row 3 only.

 

[JLGWhiz]

Well, without either seeing your worksheets or getting a better description of the data layout I have no way of troubleshooting it. All my tests were based on the description already given. Sorry about that!