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

Panda514

New Member
Joined
Mar 11, 2019
Messages
39
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
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.
Rich (BB code):
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

New Member
Joined
Mar 11, 2019
Messages
39
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.
Rich (BB code):
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.
 

Attachments

  • Capture.PNG
    Capture.PNG
    188.3 KB · Views: 3

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,204
Messages
5,623,352
Members
415,969
Latest member
Rey99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top