Moving Information In A Row From One Sheet To Another Based On Drop Down

mjramey79

New Member
Joined
Nov 14, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Let me start by saying the I am a novice when it comes to Excel. I know the basics and that is about it............


What I am looking to accomplish is to have a information in a row moved to a corresponding worksheet based on the information populated in a cells drop down. The only column that would have a drop down cell would be column the "Department" column. So for example, if I select from the drop down menu "Detailing", all information in that row would move to the "Detailing" sheet. If I selected "Programming", all information in the row would move to the "Programming" sheet. If I selected "Shop" all information in that row would move to the the "Shop" sheet. Thanks for any help that you can give.

1605370383266.png
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,099
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column B.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    With Sheets(Target.Value)
        Target.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
    End With
    Application.ScreenUpdating = True
End Sub
 

mjramey79

New Member
Joined
Nov 14, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Thanks for your help. I tried this code and it worked, however, I would like for it to do a couple of more things. First, I would like to delete the row out of the sheet that it was previously in. Second, I would like to be able to move the rows between the sheets. For example, move it from detailing to programming then move it to shop and then move it back to detailing if required. Right now if I move it to one sheet, I cant move it to another using this macro. Is there something that I am missing
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,099
Since the macro has to work on more than one sheet, the approach has to be slightly different. Is the "Jobs" sheet included in your drop down list? Are there any sheets in your workbook that are not included in the drop down list?
 

mjramey79

New Member
Joined
Nov 14, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The only items in the drop down are "Not Started", "Detailing", "Programming" & "Shop". If "Not Started" is selected from the drop down, the row will stay in the "Jobs" sheet. All other selections will move to the corresponding sheets. I hope that makes sense.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,099
Delete the previous macro. Place the macro below in the code module for ThisWorkbook. Do the following: 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 macro into the empty window that opens up. Close the window to return to your sheet. This should now work on all sheets.
VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target <> "Not Started" Then
        With Sheets(Target.Value)
            Target.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            Rows(Target.Row).Delete
        End With
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
 

mjramey79

New Member
Joined
Nov 14, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This code did exactly what I wanted it to do. However, is one last issue that I have encountered. When I go to delete the row out of any of the sheets that I move the information to, I get an error (see below). This error then prevents me from using the code.

1605558798457.png
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,099
Try:
VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target <> "Not Started" Then
        With Sheets(Target.Value)
            Target.EntireRow.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            Rows(Target.Row).Delete
        End With
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
 

mjramey79

New Member
Joined
Nov 14, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Works like a charm! Thanks for the time that you took. I really appreciate it.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,262
Messages
5,595,161
Members
413,972
Latest member
emiguy

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