VBA - Move row to multiple sheets in another workbook based on values in 2 columns

[Panda514]

Hello, I would like to move a row to multiple sheets in another workbook based on values in 2 columns. Please help me

Workbook1
-There are multiple sheets with the same columns. The sheet names differ by date. I would like on any sheet in Workbook1, when the value in Column A-"PCN Reactivation" is changed to "Yes" then the entire row is moved based on a value in Column B-"Cohort" to Worksheet2. Column B would have values 1, 2, 3, 4, 5.

Examples
-If Column A is changed to "Yes" on any sheet and the value in Column B is 1 then move the entire row to the next row on sheet names "Total Cohorts" and "Cohort1" in Workbook2.
-If Column A is changed to "Yes" on any sheet and the value in Column B is 2 then move the entire row to the next row on sheet names "Total Cohorts" and "Cohort2" in Workbook2.
-If Column A is changed to "Yes" on any sheet and the value in Column B is 3 then move the entire row to the next row on sheet names "Total Cohorts" and "Cohort3&5" in Workbook2.
-If Column A is changed to "Yes" on any sheet and the value in Column B is 4 then move the entire row to the next row on sheet names "Total Cohorts" and "Cohort4" in Workbook2.

Workbook2
-Sheet names are Total Cohorts, Cohort1, Cohort2, Cohort3&5, Cohort4

 

[mumps]

Make sure that both workbooks are open. Place this macro in the code module for ThisWorkbook. Do the following: In Workbook1, hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the code below into the empty window that opens up. Change the workbook name (in red) to suit your needs. Close the window to return to your sheet. Enter "Yes" in a cell in column A and press the RETURN key or TAB key.

Dim val As String
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    If Target = "PCN Reactivation" Then val = "PCN Reactivation"
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Dim WB As Workbook
    Set WB = Workbooks("Workbook2.xlsx")
    If val = "PCN Reactivation" Then
        If Target = "Yes" Then
            Select Case Target.Offset(, 1).Value
                Case Is = 1
                    With WB
                        Target.EntireRow.Copy .Sheets("Total Cohorts").Cells(.Sheets("Total Cohorts").Rows.Count, "A").End(xlUp).Offset(1)
                        Target.EntireRow.Copy .Sheets("Cohort1").Cells(.Sheets("Cohort1").Rows.Count, "A").End(xlUp).Offset(1)
                        Target.EntireRow.Delete
                    End With
                Case Is = 2
                    With WB
                        Target.EntireRow.Copy .Sheets("Total Cohorts").Cells(.Sheets("Total Cohorts").Rows.Count, "A").End(xlUp).Offset(1)
                        Target.EntireRow.Copy .Sheets("Cohort1").Cells(.Sheets("Cohort2").Rows.Count, "A").End(xlUp).Offset(1)
                        Target.EntireRow.Delete
                    End With
                Case Is = 3
                    With WB
                        Target.EntireRow.Copy .Sheets("Total Cohorts").Cells(.Sheets("Total Cohorts").Rows.Count, "A").End(xlUp).Offset(1)
                        Target.EntireRow.Copy .Sheets("Cohort1").Cells(.Sheets("Cohort3").Rows.Count, "A").End(xlUp).Offset(1)
                        Target.EntireRow.Copy .Sheets("Cohort1").Cells(.Sheets("Cohort5").Rows.Count, "A").End(xlUp).Offset(1)
                        Target.EntireRow.Delete
                    End With
                Case Is = 4
                    With WB
                        Target.EntireRow.Copy .Sheets("Total Cohorts").Cells(.Sheets("Total Cohorts").Rows.Count, "A").End(xlUp).Offset(1)
                        Target.EntireRow.Copy .Sheets("Cohort1").Cells(.Sheets("Cohort4").Rows.Count, "A").End(xlUp).Offset(1)
                        Target.EntireRow.Delete
                    End With
            End Select
        End If
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[Panda514]

Make sure that both workbooks are open. Place this macro in the code module for ThisWorkbook. Do the following: In Workbook1, hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the code below into the empty window that opens up. Change the workbook name (in red) to suit your needs. Close the window to return to your sheet. Enter "Yes" in a cell in column A and press the RETURN key or TAB key.

Dim val As String
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    If Target = "PCN Reactivation" Then val = "PCN Reactivation"
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Dim WB As Workbook
    Set WB = Workbooks("Workbook2.xlsx")
    If val = "PCN Reactivation" Then
        If Target = "Yes" Then
            Select Case Target.Offset(, 1).Value
                Case Is = 1
                    With WB
                        Target.EntireRow.Copy .Sheets("Total Cohorts").Cells(.Sheets("Total Cohorts").Rows.Count, "A").End(xlUp).Offset(1)
                        Target.EntireRow.Copy .Sheets("Cohort1").Cells(.Sheets("Cohort1").Rows.Count, "A").End(xlUp).Offset(1)
                        Target.EntireRow.Delete
                    End With
                Case Is = 2
                    With WB
                        Target.EntireRow.Copy .Sheets("Total Cohorts").Cells(.Sheets("Total Cohorts").Rows.Count, "A").End(xlUp).Offset(1)
                        Target.EntireRow.Copy .Sheets("Cohort1").Cells(.Sheets("Cohort2").Rows.Count, "A").End(xlUp).Offset(1)
                        Target.EntireRow.Delete
                    End With
                Case Is = 3
                    With WB
                        Target.EntireRow.Copy .Sheets("Total Cohorts").Cells(.Sheets("Total Cohorts").Rows.Count, "A").End(xlUp).Offset(1)
                        Target.EntireRow.Copy .Sheets("Cohort1").Cells(.Sheets("Cohort3").Rows.Count, "A").End(xlUp).Offset(1)
                        Target.EntireRow.Copy .Sheets("Cohort1").Cells(.Sheets("Cohort5").Rows.Count, "A").End(xlUp).Offset(1)
                        Target.EntireRow.Delete
                    End With
                Case Is = 4
                    With WB
                        Target.EntireRow.Copy .Sheets("Total Cohorts").Cells(.Sheets("Total Cohorts").Rows.Count, "A").End(xlUp).Offset(1)
                        Target.EntireRow.Copy .Sheets("Cohort1").Cells(.Sheets("Cohort4").Rows.Count, "A").End(xlUp).Offset(1)
                        Target.EntireRow.Delete
                    End With
            End Select
        End If
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Thank you so much for your help! I made a sample version with Test.xlsm and Test2.xlsm to see if this would work for me. I followed your directions and renamed Workbook2. I'm not sure what I'm doing wrong. When I type Yes in Column A, it doesn't move the rows to the other workbook.

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual files. Perhaps you could upload a copy of both files to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to the files that you can post here. If the workbooks contains confidential information, you could replace it with generic data.