Sub won't work on change event

Greetings, Gurus.

I have a small macro that filters and copied data to a "Main" sheet. It works beautifully if I run it from a standard module, but when I try to run it from a Worksheet Change event, it always crashes Excel. Here is the code:

Sub Get_Data()
    Application.ScreenUpdating = False
    Dim tbl As Range
    Dim Loc
    Sheets("Main").Select
    Loc = Range("D2").Value
    
    ActiveSheet.Unprotect
    Range("A6:K11").ClearContents
    Range("A15:K21").ClearContents

    Sheets("MDC sched").Select
    Range("A1").AutoFilter Field:=1, Criteria1:=Loc
    Set tbl = Range("A1").CurrentRegion
    Application.DisplayAlerts = False
    tbl.Offset(1, 0).Resize(tbl.Rows.Count, _
        tbl.Columns.Count).Copy
    
    Sheets("Main").Select
    Range("A6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("MDC sched").Select
    Range("A1").Select
    Application.CutCopyMode = False
    Application.DisplayAlerts = True
    ActiveSheet.AutoFilterMode = False
    
    Sheets("USA").Select
    Range("A1").AutoFilter Field:=1, Criteria1:=Loc
    Set tbl = Range("A1").CurrentRegion
    Application.DisplayAlerts = False
    tbl.Offset(1, 0).Resize(tbl.Rows.Count, _
        tbl.Columns.Count).Copy
    
    Sheets("Main").Select
    Range("A15").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("USA").Select
    Range("A1").Select
    Application.CutCopyMode = False
    Application.DisplayAlerts = True
    ActiveSheet.AutoFilterMode = False
    
    Sheets("Main").Select
    Range("D2").Select
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False
    Application.ScreenUpdating = True
End Sub

Now, I've never run a macro from a worksheet change event, but I read some examples on here, so I right clicked on the "Main" worksheet, and copied my code between these lines:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Of course, I removed "Sub Get_Data()" from the top, and "End Sub" from the bottom, but as I said, Excel keeps crashing. What am I doing wrong?

Thanks in advance for any help you can offer.

I don't know how you've actually coded this, but you need to disable events (application.enablevents=false) before doing anything (like pasting data) that will trigger the change event again, and then re-enable events when you're done.

Post the code your using, and say which line falls over.

Hutch,

In the Main sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$4" Then GetData
End Sub

In the code module:

Sub GetData()
    Dim Loc         As Variant

    On Error GoTo Oops
    Application.EnableEvents = False

    With Worksheets("Main")
        Loc = .Range("D4").Value
        .Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, UserInterfaceOnly:=True
        .Range("A8:K13, A17:I17, A21:G21").ClearContents
    End With

    With Worksheets("MDC sched")
        .Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, UserInterfaceOnly:=True
        .Range("A1").AutoFilter Field:=1, Criteria1:=Loc
        .Range("A1").CurrentRegion.Offset(1).Copy
        Worksheets("Main").Range("A8").PasteSpecial Paste:=xlPasteValues
        .AutoFilterMode = False
    End With

    With Worksheets("USA")
        .Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, UserInterfaceOnly:=True
        .Range("A1").AutoFilter Field:=1, Criteria1:=Loc
        .Range("A1").CurrentRegion.Offset(1).Copy
        Worksheets("Main").Range("A17").PasteSpecial Paste:=xlPasteValues
        .AutoFilterMode = False
    End With

    With Worksheets("GdMrk Contacts")
        .Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, UserInterfaceOnly:=True
        .Range("A1").AutoFilter Field:=1, Criteria1:=Loc
        .Range("A1").CurrentRegion.Offset(1).Copy
        Worksheets("Main").Range("A21").PasteSpecial Paste:=xlPasteValues
        .AutoFilterMode = False
    End With

OutaHere:
    Application.CutCopyMode = False
    Application.EnableEvents = True
    Exit Sub

Oops:
    MsgBox "Error!"
    Resume OutaHere
End Sub

All completely untested ...