run-time error - automation error the object has disconnected from its clients

[mwoody1983]

Hello,

I wonder if someone could help me. I'm completely stumped.

I have a file the following macro works in. The second file I have in exactly the same format gets the error I have in the title.

The macro is below. Any advice greatly appreciated:

#
Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range, rng2 As Range
Set rng1 = Range("F9:F12")
On Error Resume Next
Set rng2 = Intersect(rng1, Target).Cells(1)
On Error GoTo 0
If Not rng2 Is Nothing Then
Application.EnableEvents = False
Range(rng2.Offset(1), rng1.Cells(rng1.Count)).Value = "All"
Range("F12:F13").ClearContents
Application.EnableEvents = True
End If


Dim rng3 As Range, rng4 As Range
Set rng3 = Range("F14:F16")
On Error Resume Next
Set rng4 = Intersect(rng3, Target).Cells(1)
On Error GoTo 0
If Not rng4 Is Nothing Then
Application.EnableEvents = False
Range(rng4.Offset(1), rng3.Cells(rng3.Count)).Value = "*Select Question*"
Range("F16:F17").ClearContents
Application.EnableEvents = True
End If


'**************************************************************************************************
'**************************************************************************************************
Dim region_range As Range
Dim zone_range As Range
Dim district_range As Range


Set region_range = Range("f9")
Set zone_range = Range("f10")
Set district_range = Range("f11")


If ActiveSheet.AutoFilterMode = False Then


Range("a21:c21").AutoFilter


End If


If Range("f15") = "*Select Question*" Then
ActiveSheet.AutoFilterMode = False


ElseIf region_range = "All" Then
ActiveSheet.AutoFilterMode = False


ElseIf region_range <> "All" And zone_range = "All" Then
ActiveSheet.AutoFilterMode = False
Range("a21:c21").AutoFilter
ActiveSheet.Range("$a$21").AutoFilter Field:=1, Criteria1:= _
region_range

ElseIf region_range <> "All" And zone_range <> "All" And district_range = "All" Then
ActiveSheet.Range("$a$21").AutoFilter Field:=1, Criteria1:= _
region_range

ActiveSheet.Range("$a$21").AutoFilter Field:=2, Criteria1:= _
zone_range

ActiveSheet.Range("$A$21").AutoFilter Field:=3


ElseIf region_range <> "All" And zone_range <> "All" And district_range <> "All" Then
ActiveSheet.Range("$a$21").AutoFilter Field:=1, Criteria1:= _
region_range

ActiveSheet.Range("$a$21").AutoFilter Field:=2, Criteria1:= _
zone_range

ActiveSheet.Range("$a$21").AutoFilter Field:=3, Criteria1:= _
district_range

End If


End Sub




Sub Worksheet_Calculate()










Dim KeyCell As Range




Set KeyCell = Range("eg1")


If ActiveSheet.AutoFilterMode = True Then


Range("a21:c21").AutoFilter


End If




If Not Application.Intersect(KeyCell, Range("eg1")) _
Is Nothing Then
Range(Range("eg1").Value).Copy
Range("a22").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False




'Range("d21").Select


End If


End Sub
#