This code is working when a paste is done it will force the formatting to be the same as in the destination sheet and will remove space and ChrW from the cells as soon as the paste event is detected. Is there any way to improve the code to work better?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim cell As Range

On Error Resume Next

    With Application.CommandBars("Standard").Controls("&Undo")
        If .ListCount < 1 Then Exit Sub '<--- this line creates an error if on error resume next is commented when reset filter is pressed
        If .List(1) <> "Paste" Then Exit Sub
    End With

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Selection.Replace ChrW(160), " ", xlPart

    For Each cell In Selection
        cell.Value2 = WorksheetFunction.Trim(WorksheetFunction.Clean(cell.Value2))
    Application.CutCopyMode = False
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

And this is the code for the reset filter:

Sub resetFilters()

Dim sht As Worksheet
Dim LastRow As Range

Application.ScreenUpdating = False

   On Error Resume Next
        If ActiveSheet.FilterMode Then
  End If

Application.ScreenUpdating = True
Call GetLastRow

End Sub

2 Answers 2


On Error Resume Next is not some kind of magic that makes bugs go away. It simply hides them and makes your code completely unpredictable.

Let me repeat that, because it's of the utmost importance....

On Error Resume Next makes your code completely unreliable, unpredictable, and unsafe. Once an error occurs, your program is left in an unknown and invalid state, but continues to chug along pretending everything is fine. In the meantime, totally wrecking all your data.

So, remove that evil line of code, take the time to recreate the error, figure out why it's happening, then fix it. Once you've done that, come back and we'd be happy to help you turn your "good enough" code into Good Code™.


I'm on my cell so I apologize for any mistakes.

The first IF statement has an error because you have initiated a with statement that is immediately evaluated on a count of that object. Instead of if .listcount<1 use a variable for the .listcount of the object:

Count = object.listcount

Then use the WITH OBJECT statement. Then use:

IF count < 1 then... :end 
with: exit sub
  • \$\begingroup\$ Tried and if I lose the on error resume next it's giving me errors. Tried different approaches and still didn't work still researching online to see if I can find a solution for the problem. \$\endgroup\$ Apr 2, 2017 at 16:40
  • \$\begingroup\$ Above the 'dim cell as range', I put 'set target = selection'. I also removed the on error resume next portion. This worked to preserve the formatting on the selection of the pasted area. If you are considering a new approach, you may want to move this to a vbaproject module (instead of a workbook module) because you have nothing distinguishing a workbook event just the users actions for paste. I think it would make your project easier for you to troubleshoot and build off of. \$\endgroup\$ Apr 3, 2017 at 17:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.