excel vba - Macro to copy filtered data on cell change

Question

I'm trying to write a macro to do the following:

from Sheet1 watch the A column for the data I input;
when I write something in a cell in the A column use that value to filter Sheet2;
after the filter is done, copy everything except the column header from the second sheet into the first one, even if there are multiple values;
as a bonus it could copy everything except from column A from Sheet2 and copy starting from Column B in Sheet1, which is what I attempted.

I tried writing this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("A:A")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
        MsgBox "Cell " & Target.Address & " has changed. New value is: " _
            & Target.Value
        copy_filter (Target)
    End If
End Sub

Sub copy_filter(Changed)
    Worksheets("Sheet2").Select

    With Worksheets("Sheet2")
        With .Range("$A$1:$L$5943")
            .AutoFilter Field:=1, Criteria1:=Changed.Value
            .SpecialCells(xlCellTypeVisible).Select
            Selection.Offset(1, 0).Copy
        End With
    End With

    Worksheets("Sheet1").Select
    Worksheets("Sheet1").Range(Changed.Address).Offset(0, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues

    Application.CutCopyMode = False
End Sub

The first Private Sub watches for change events on Sheet1, I put it into the sheet module, not the Workbook module, and it works, since the box gets shown correctly, however on the second Sub I get an error on this line: .AutoFilter Field:=1, Criteria1:=Changed.Value, the error says: Run-time error '424': Object required.

I can't figure out what I'm doing wrong.

cyboashu cyboashu · Accepted Answer · 2016-07-15T16:51:17

Change copy_filter (Target) to either

call copy_filter (Target)

OR

copy_filter Target

Read this: https://msdn.microsoft.com/en-us/library/office/gg251432.aspx

excel vba - Macro to copy filtered data on cell change

1 Answers