vba error - object has disconnected from its client

[mwoody1983]

Hi,

could someone help please.

I get the following error: automation error the object has disconnected from its clients:


The two pieces of code on their own when the other is commented out work as expected. In another file I have both together work as I need.

However in my current file, same format, they don't work and I get the above error.

Please see the code below:

[COLOR=#333333]Option Explicit[/COLOR]

[COLOR=#333333]Dim region_range As Range[/COLOR]
[COLOR=#333333]Dim zone_range As Range[/COLOR]
[COLOR=#333333]Dim district_range As Range[/COLOR]


[COLOR=#333333]Set region_range = Range("f9")[/COLOR]
[COLOR=#333333]Set zone_range = Range("f10")[/COLOR]
[COLOR=#333333]Set district_range = Range("f11")[/COLOR]


[COLOR=#333333]If ActiveSheet.AutoFilterMode = False Then[/COLOR]


[COLOR=#333333]Range("a21:c21").AutoFilter[/COLOR]


[COLOR=#333333]End If[/COLOR]


[COLOR=#333333]If Range("f15") = "*Select Question*" Then[/COLOR]
[COLOR=#333333]ActiveSheet.AutoFilterMode = False[/COLOR]


[COLOR=#333333]ElseIf region_range = "All" Then[/COLOR]
[COLOR=#333333]ActiveSheet.AutoFilterMode = False[/COLOR]


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

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

[COLOR=#333333]ActiveSheet.Range("$a$21").AutoFilter Field:=2, Criteria1:= _[/COLOR]
[COLOR=#333333]zone_range[/COLOR]

[COLOR=#333333]ActiveSheet.Range("$A$21").AutoFilter Field:=3[/COLOR]


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

[COLOR=#333333]ActiveSheet.Range("$a$21").AutoFilter Field:=2, Criteria1:= _[/COLOR]
[COLOR=#333333]zone_range[/COLOR]

[COLOR=#333333]ActiveSheet.Range("$a$21").AutoFilter Field:=3, Criteria1:= _[/COLOR]
[COLOR=#333333]district_range[/COLOR]

[COLOR=#333333]End If[/COLOR]


[COLOR=#333333]End Sub[/COLOR]




[COLOR=#333333]Sub Worksheet_Calculate()[/COLOR]










[COLOR=#333333]Dim KeyCell As Range[/COLOR]




[COLOR=#333333]Set KeyCell = Range("eg1")[/COLOR]


[COLOR=#333333]If ActiveSheet.AutoFilterMode = True Then[/COLOR]


[COLOR=#333333]Range("a21:c21").AutoFilter[/COLOR]


[COLOR=#333333]End If[/COLOR]




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




[COLOR=#333333]'Range("d21").Select[/COLOR]


[COLOR=#333333]End If[/COLOR]


[COLOR=#333333]End Sub[/COLOR]

Any help will be greatly appreciated, i've been stuck on this problem for two days?