Macro help

[aholloway21]

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.

 

[Fluff]

Hi & welcome to MrExcel.
How about

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]

Hi & welcome to MrExcel.
How about

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]

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]

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?

 

[aholloway21]

Doesn't want to show the image, so link below;
https://ibb.co/nDS9pH

 

[Fluff]

That seem to be in the right place.
Firstly run this

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

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