VBA Code not working

[SantanaKRE8s]

I Have a VBA code set up in one of my sheets and it works to remove complete rows and paste them on a diferent sheet. I wanted to automated so that it would do this when I select Resolved from a drop down list, but that VBA to automate this action is not working. one of my columsn just flashes when I select Resoved from the frop down list.

Private Sub Worksheet_Change(ByVal Target As Range)
'
Dim Z As Long
Dim xVal As String
On Error Resume Next
If Intersect(Target, Range("S:S")) Is Nothing Then
Application.EnableEvents = False
For Z = 1 To Target.Count
If Target(Z).Value > 0 Then
Call MoveBasedOnValue
End If
Next
End If
Application.EnableEvents = True
End Sub

 

[SantanaKRE8s]

Also there is two other option in Column S there is " Pending ", " Work in Progress " and "Resolved"

 

[Joe4]

just want to mention that the row it cuts from Backlog and sends to resolved sheet goes straight to the next blank space.

Not sure what you mean. Can you show me an example of:
a. what it is currently doing on the Backlog sheet
b. what it should be doing on the Backlog sheet

Also I would like to past only value and not the format so that I don not loose the format in REsolved page

How do the formats between the two sheets differ?

Also there is two other option in Column S there is " Pending ", " Work in Progress " and "Resolved"

Not sure why that matters. It will only moved records changed to "Resolved", just as you requested. It will ignore the other ones.

 

[Joe4]

I am thinking maybe you posted your last two replies before you saw my code and tried it out.
I think it should do most of what you want, except maybe for the formatting part.
If I could just get clarification on that part, that would be great.

 

[SantanaKRE8s]

You are right I did, but the code is not working for some reason. I copied it exactly the way you shared it.

 

[Joe4]

You placed in the "Backlog" sheet module, right?
If you place it anywhere else, it will not work.

Also, it is possible that you had an interruption in an earlier code run, and your events are currently in a disabled state.
You can manually run this code to turn them back on.

Sub ReEnableEvents()
    Application.EnableEvents = True
End Sub

 

[SantanaKRE8s]

Where Do I put the reenable code

 

[Joe4]

Where Do I put the reenable code

It doesn't matter with this one. You can put this is any module, and then just run it manually.

 

[SantanaKRE8s]

Ok reenable code got it to work, But I still want to paste only values in Resolved sheet, so the Resolved sheet format does not change.

 

[SantanaKRE8s]

In the Rsolved Sheet, I have all cells dark Gray and font is black so when, But when the rows are transfered over there is formatting attached that I use to color code cells that have dates that are past due or in certain date range. So I dont want any formatting transferred over. Attached is an example of each page. I would like the Resolved page to stay dark gray when the rows are transferred over.

 

[Joe4]

Try this version then:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   See if any updates to column S
    Set rng = Intersect(Range("S:S"), Target)
    
'   Exit if no updates to column S
    If rng Is Nothing Then Exit Sub
    
'   Loop through values updated in column S
    Application.EnableEvents = False
    For Each cell In rng
'       See if cell value is Resovled
        If cell.Value = "Resolved" Then
'           Copy to bottom of resolved sheet
            Rows(cell.Row).Copy
            Sheets("Resolved").Range("A" & Sheets("Resolved").UsedRange.Rows.Count + 1).PasteSpecial _
                Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
'           Remove from backlog sheet
            Rows(cell.Row).Delete
        End If
    Next cell
    Application.EnableEvents = True
    
End Sub