aholloway21

New Member
Joined
Apr 23, 2018
Messages
4
Hi All,

Would really appreciate some help if anyone can provide.
I'm not great with Excel but will try and explain as best as I can.

My spreadsheet is used for tracking "Jobs in progress", so it's a live summary of all work going through.
I have 3 sheets which are copies of each other, the first Active the second Completed, the third Dead.

They run from columns B-N. Column H contains a drop down selection for showing the stage of any job. What I am looking to do is when a certain option in that drop down is selected. For a specific example, "Completed". I then want all the data in that row to copy to the Compete sheet, and delete itself from the active sheet.
This would also then be replicated for the drop down option "Dead" to go to the sheet named Dead.

I hope I have explained this in a way that makes sense.



Thank you in advance.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,392
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.CountLarge > 1 Then Exit Sub
   If Not Target.Column = 8 Then Exit Sub
Application.EnableEvents = False
   Select Case LCase(Target.Value)
      Case "completed"
         Target.EntireRow.Copy Sheets("Completed").Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
         Target.EntireRow.Delete
      Case "dead"
         Target.EntireRow.Copy Sheets("Dead").Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
         Target.EntireRow.Delete
   End Select
Application.EnableEvents = True
End Sub
This needs to go in the sheet module
 

aholloway21

New Member
Joined
Apr 23, 2018
Messages
4
Hi & welcome to MrExcel.
How about
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.CountLarge > 1 Then Exit Sub
   If Not Target.Column = 8 Then Exit Sub
Application.EnableEvents = False
   Select Case LCase(Target.Value)
      Case "completed"
         Target.EntireRow.Copy Sheets("Completed").Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
         Target.EntireRow.Delete
      Case "dead"
         Target.EntireRow.Copy Sheets("Dead").Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
         Target.EntireRow.Delete
   End Select
Application.EnableEvents = True
End Sub
This needs to go in the sheet module

Thank you very much for the quick response. However, it doesn't seem to be working. Unless I am being dense with setting it up, which I think I have done correctly.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,392
Office Version
  1. 365
Platform
  1. Windows
Did you put it in the "Jobs in progress" sheet module.
Right click on the sheet tab > View Code. Is the code in the window that opens up?
Also are your Drop downs data validation drop downs?
 

aholloway21

New Member
Joined
Apr 23, 2018
Messages
4

ADVERTISEMENT

Did you put it in the "Jobs in progress" sheet module.
Right click on the sheet tab > View Code. Is the code in the window that opens up?
Also are your Drop downs data validation drop downs?

They are validation drop downs.
If it helps, here is a screen shot showing the code on the sheet module, maybe you can spot something I'm not seeing?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,392
Office Version
  1. 365
Platform
  1. Windows
That seem to be in the right place.
Firstly run this
Code:
Sub Chk()
Application.EnableEvents = True
End Sub
Then try changing one of the dropdowns.
If that doesn't add this line of code as shown
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=#ff0000]Stop[/COLOR]
   If Target.CountLarge > 1 Then Exit Sub
and change a dropdown. Does the editor come up with the word Stop highlighted in yellow?
If Yes, then step through the code using F8 & see what happens.

I notice that you are on a Mac, which may be the problem as Macs don't have the full range of VBA
 

Watch MrExcel Video

Forum statistics

Threads
1,109,082
Messages
5,526,741
Members
409,717
Latest member
Oscarsalone

This Week's Hot Topics

Top