Error 1004

[Rob_010101]

Private Sub Worksheet_Change(ByVal Target As Range)
 
 On Error Resume Next
    Sheet1.ShowAllData
  On Error GoTo 0
 
  Dim fromRow%
  Dim archiveRow%
  Dim strMatch As String
  Dim wsTarget As Worksheet   'sheet to move data to
  Dim blnMove As Boolean      'whether to move data or not
  Dim blnOnlyValues As Boolean   'determine if it´s the arvjice
    
  If Target.Cells.Count > 1 Then Exit Sub

  If Not Application.Intersect(Target, Range("AI2:AI200")) Is Nothing Then 'amend this range address to your
    blnOnlyValues = False
    Select Case UCase(Target.Value)       'as you have given both "closed" and "Closed"
      Case "LEAVER"
        Set wsTarget = ThisWorkbook.Worksheets("Leavers")
        blnMove = True
        blnOnlyValues = False
          End Select
    If blnMove Then
       'section of code is taken from your posting
      fromRow = ActiveCell.Row
      With wsTarget     'only change made here
        If .FilterMode Then
          strMatch = "match" & Replace("(2,1/(a:a>""""),1)", "a:a", .AutoFilter.Range.Cells(1).EntireColumn.Address(0, 0, 1, 1))
          archiveRow = Evaluate(strMatch) + 1
        Else
          archiveRow = wsTarget.Cells(wsTarget.Rows.Count, 1).End(3).Row + 1
        End If
      End With
Range(Cells(fromRow, 1), Cells(fromRow, 8)).Copy wsTarget.Cells(archiveRow, 1)
With wsTarget                                                                     '<-- added
    .Range(.Cells(archiveRow, 1), .Cells(archiveRow, 1)).FormatConditions.Delete '<-- added
End With                                                                          '<-- added
If blnOnlyValues Then wsTarget.Cells(archiveRow, 1).Resize(1, 20).Value = Cells(fromRow, 1).Resize(1, 20).Value
Application.EnableEvents = False      '<-- added
Rows(fromRow).EntireRow.Delete
Application.EnableEvents = True       '<-- added
Set wsTarget = Nothing
    End If
  End If

End Sub

Hi

The above has been working perfectly for ages and now suddenly this error has appeared

on the "Sheet1.ShowAllData" line

I have recently created another spreadsheet which uses VSTACKED =SORT(UNIQUE(FILTER to pull data from this. Would this cause it?

 

[Domenic]

It looks like you have your error trapping set to Break on All Errors. Instead, try setting it to Break on Unhandled Errors . . .

Visual Basic Editor >> Tools >> Options >> General >> Error Trapping >> click/select Break on Unhandled Errors >> OK

Hope this helps!

 

[Rob_010101]

Thank you!

 

[Domenic]

You're very welcome, cheers!