Runtime-time - Method 'Range' of object'_Worksheet' failed

[Lorna]

I'm hoping one of you experts out there can help me with this problem.

When I run the code I have posted below, as part of a Workwheet_Change event, it keeps it returning the following error:

Runtime-time error '1004
Method 'Range' of object'_Worksheet' failed

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Address <> "$C$27" Then Exit Sub
If Target.Address(False, False) <> "C27" Then Exit Sub
If Intersect(Target, Range("C27")) Is Nothing Then Exit Sub
 
 
Application.Goto Reference:="OtherClearRange"
Selection.ClearContents 'Clears range of previous extracted data
 
'This is the section of code where the Runtime Error Occurs
Application.Goto Reference:="InputData"
Range("InputData").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("Criteria"), CopyToRange:=Range("Extract"), Unique:=True
 
End Sub

However, I can run the same code as a macro attached to a button and it works fine without any errors.

Any ideas as to why I keep getting this runtime error and what can I do to fix it?

Thanks a million times to anyone who would be willing to help me on this!

 

[Tom Urtis]

First, these 2 lines of code are superfluous so just delete them:

If Target.Address(False, False) <> "C27" Then Exit Sub
If Intersect(Target, Range("C27")) Is Nothing Then Exit Sub


Second, a hunch is, the error is due to the range "InputData" existing on a sheet other than the sheet whose module this Change event is in.

Otherwise, #1004 can occur when the sheet is protected when a cell or range is attempted to be changed.

Or maybe merged cells in one of the ranges.

Hard to diagnose from a distance but we can start with the above suggestions and go from there.

Offer up some details to put this in context, and post back if any of these hunches are correct.

 

[Lorna]

Tom,

Thanks so, so much! I have been hung up on this for weeks now. I simply moved my input data, criteria range, and extract range to the sheet where the change event occurs and the code finally runs. YEAH!!

Now there's a new problem which I know has a quick and easy fix, I'm just not sure what it is.

I have a series of four dropdown boxes with cascading data on the sheet where the code is attached. The second and third dropdowns use the Indirect fuction as the data source. The whole purpose of this last code was to filter data data for the fourth dropdown box. Now each time I select data from any of the dropdowns, I get a slight screen flicker. How can I get rid of that?

Thanks again for your help.

 

[Tom Urtis]

If the only problem is a screen flicker, sandwich your code between screenupdating settings, example:

Application.ScreenUpdating = False
 
'Your existing code goes here
 
Application.ScreenUpdating = True

 

[Lorna]

Thanks again. That fixed the problem.