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)) Next 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 ActiveSheet.ShowAllData End If Range("A3:T3").ClearContents Application.ScreenUpdating = True Call GetLastRow End Sub