Combining multiple worksheet change events into one?

[sidel801]

There are two worksheet events that I was hoping to combine. The first event is meant to keep rows filtered depending on a criteria and the other event is meant to copy formulas when a new row is added. I seem to get an error when I try to put both events in. Any help would be greatly appreciated!

Event 1: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Columns.Count < Me.Columns.Count Then Range("e10:e80").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _ ("e6:e7"), Unique:=False End If End Sub

Event 2: Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Dim lngCols(1 To 3) As Long lngCols(1) = 10 lngCols(2) = 11 lngCols(3) = 12 On Error GoTo ErrorHandler: If Target.Columns.Count <> Columns.Count Then Exit Sub For i = 1 To UBound(lngCols) Cells(Target.Offset(-1, 0).Row, lngCols(i)).Copy Cells(Target.Row, lngCols(i)).Select ActiveCell.PasteSpecial xlPasteFormulas Next i ErrorHandler: Application.ScreenUpdating = True End Sub

 

[ranman256]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngCols(1 To 3) As Long

Application.ScreenUpdating = False

If Target.Columns.Count < Me.Columns.Count Then
    Range("e10:e80").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range ("e6:e7"), Unique:=False
End If

lngCols(1) = 10
lngCols(2) = 11
lngCols(3) = 12

On Error GoTo ErrorHandler:
If Target.Columns.Count <> Columns.Count Then Exit Sub

For i = 1 To UBound(lngCols)
   Cells(Target.Offset(-1, 0).Row, lngCols(i)).Copy
   Cells(Target.Row, lngCols(i)).Select
   ActiveCell.PasteSpecial xlPasteFormulas
Next i
Application.ScreenUpdating = True
exit sub

ErrorHandler:
Application.ScreenUpdating = True
End Sub

 

[sidel801]

Wow! It worked! Thank you so much!

The one issue I'm running into now is when I add a row to the sheet, event 1 triggers and it automatically filters. It seems to work when just event 1 is showing up, but with this combined code, it seems to trigger every time a change happens. Any idea how to get around that? Not sure if that makes sense.

Really appreciate the help!