Automatically copy & paste range from different sheets based on cell value into one sheet

Hi,

I have workbook with 4 sheets. Sheet 1 named "1", sheet 2 named "2", sheet 3 named " Action Summary", sheet 4 named "dropdown". The dropdown sheet will be hidden in the final version but it is used to create a dropdown in column F on all the other sheets as well as column L on the Action Summary Sheet only.

Dropdown identified as: Poor - 1, Mediocre - 2, Good -3, Excellent - 4.

I tried multiple solutions but can't seem to get it right. I would like for the range A:L on both sheet 1 and 2 to populated on the action summary sheet if the value in the selected row in column F on the respective sheets is either Poor - 1 or Mediocre - 2. If column L in the action summary sheet is completed the row should be removed from the action summary and the adjusted outcome in column L should replace the initial outcome in column F on sheet 1 and 2 respectively. If possible the data in the action summary sheet should update automatically if a Poor - 1 or Mediocre - 2 score is captured in sheet 1 and 2 without repeating rows of data.

I hope I explained in a way that it is understood. Please help.

Final spreadsheet attached with solution.

Hallo,

Thank you for responding! I have attached the spreadsheet with with unmerged cells and less data to to make it easier to work on. The code that you are explaining is exactly what I require and it will resolve my current problem.

Once again, thank you for making the time to help me.

Hi All,
Thank you all for your willingness to help. Apologies for taking some time to respond. I tried to figure out how the code is functioning and I made some changes to the sheet taking into consideration comment from all.

With that said, I think I messed it up with my edits. I downloaded and build in the code and spreadsheet provided by Sintek. I aded from the previous version, ID in column A to have a unique identifier. I added bookmarks in the code where I think I messed up. On top of these edits, I tried to incorporate the comment from John Topley which noted that "Excellent - 4" is missing from the code.

John also asked 'Can status in "1"/"2" go from "Poor - 1 " to "Mediocre - 2" ? If so, what result is required in "Analysis Summary"? Status change ?' response: On sheets "1"/"2"/"3"/"4" the status can be "Poor - 1" / "Mediocre - 2" / "Good - 3" / "Excellent - 4" if its "Poor - 1" / "Mediocre - 2" on the sheets("1","2","3","4") then list in "Action Summary" if its "Good - 3" / "Excellent - 4" no need to list. If the adjusted score in the action manager is changed to "Good-3" or "Excellent-4" (aka in column M previous version column L) then it is removed from the "Action Summary" tab and the status is changed to the revised status on the sheets.

Sintek's code captured this functionality perfectly with my addition of "Excellent - 4" in the second part of the code. To summarise:

- I added ID to column A on all sheets to serve as unique identifier for search functions but I think I messed up.
- Code from Sintek works but needs adjustment after my edits, I bookmarked parts of the code I do not understand.
- I added more page (page "3" and "4"), these are copies of pages "1" and "2" with different headings and ID's in column A to test what I should do if I have more pages, which is expected.

I require a review of the code again just to see where I went wrong and to make column A as the identifier. Attached is revised version.

I am assuming that with Sheets 1 & 2 the data in Col D is unique and can be used to find said data if Action Summary Col L is changed...
Or...Are you going to add a column showing where data comes from when moved to Action Summary...

Also...Never use merged cells...Just causes havoc with VBA...See amended upload sample...All merged cells removed...
Should do all required...Code in This WorkBook Module...Based on Col D as unique...
Code fires...
When making changes in Col F of Sheets 1& 2
When making changes in Col L of Sheets Action Summary

Private Sub Workbook_SheetChange(ByVal ws As Object, ByVal Target As Range)
Dim Arr, Fnd As Range
If Target.CountLarge > 1 Then Exit Sub
If ws.Name = "1" Or ws.Name = "2" Then
    With ws.Cells(1).CurrentRegion
        If Not Intersect(Target, .Columns(6)) Is Nothing Then
            If Target = "Poor - 1 " Or Target = "Mediocure - 2" Then
                Application.EnableEvents = False
                Arr = Application.Index(.Value, Target.Row, Application.Transpose(Array(1, 2, 3, 4, 5, 6, 8, 9, 10, 11, 12)))
                With Sheets("Action Summary").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 11)
                    .Value = Application.Transpose(Arr)
                    .Resize(, 12).Borders.Weight = 2
                    .Columns(12).Validation.Add xlValidateList, , , "Good - 3,Excellent - 4"
                End With
                Application.EnableEvents = True
            End If
        End If
    End With
ElseIf ws.Name = "Action Summary" Then
    If Not Intersect(Target, ws.Range("L:L")) Is Nothing Then
        If Target = "Good - 3" Then
            Application.EnableEvents = False
            Set Fnd = Sheets("1").Range("D:D").Find(Target.Offset(, -8), , xlValues, xlWhole)
            If Fnd Is Nothing Then Set Fnd = Sheets("2").Range("D:D").Find(Target.Offset(, -8), , xlValues, xlWhole)
            Fnd.Offset(, 2) = Target: Target.EntireRow.Delete
            Application.EnableEvents = True
        End If
    End If
End If
End Sub

Following from Sintek's code and comments:

You need a unique identifier for each transaction (rather than using column D text ) on tabs "1" and "2" so the code can check if a record already exits in "Action Summary" and take appropriate action.

Can status in "1"/"2" go from "Poor -1 " to "Mediocre -2" ? If so, what result is required in "Analysis Summary"? Status change ?

Code currently does not check for "Excellent - 4" in "Analysis Summary"

Sintek's code captured this functionality perfectly with my addition of "Excellent - 4"

Was not sure if that was also an requirement...Your amendment was close...see blue below

This slight amendment solves...I assume the sheets are always numbers 1 to 4...red below is if that is the case...If not then red snippets won't work...

Private Sub Workbook_SheetChange(ByVal ws As Object, ByVal Target As Range)
Dim Arr, Fnd As Range, i As Long
If Target.CountLarge > 1 Then Exit Sub
If IsNumeric(ws.Name) Then
    With ws.Cells(1).CurrentRegion
        If Not Intersect(Target, .Columns(7)) Is Nothing Then
            If Target = "Poor - 1" Or Target = "Mediocure - 2" Then
                Application.EnableEvents = False
                Arr = Application.Index(.Value, Target.Row, Application.Transpose(Array(1, 2, 3, 4, 5, 6, 8, 9, 10, 11, 12)))
                With Sheets("Action Summary").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 11)
                    .Value = Application.Transpose(Arr)
                    .Resize(, 12).Borders.Weight = 2
                    .Columns(13).Validation.Add xlValidateList, , , "Good - 3,Excellent - 4"
                End With
                Application.EnableEvents = True
            End If
        End If
    End With
ElseIf ws.Name = "Action Summary" Then
    If Not Intersect(Target, ws.Range("M:M")) Is Nothing Then
        If Target = "Good - 3" Or Target = "Excellent - 4" Then
            Application.EnableEvents = False
            For i = 1 To 4
                Set Fnd = Sheets("" & i & "").Range("A:A").Find(Target.Offset(, -12), , xlValues, xlWhole)
                If Not Fnd Is Nothing Then Fnd.Offset(, 6) = Target: Target.EntireRow.Delete: Exit For
            Next i
            Application.EnableEvents = True
        End If
    End If
End If
End Sub

.........................
Thanks.png