Runtime Error 1004

[Sandy]

I am receiving the following error for the code listed below:
'Run-time error 1004'
'Application-defined or object-defined error'

'Sub filter()

Sheets("RecordOfRoundsDetailed").Range("AllDetailedRecords"). _
AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("RecordOfRoundsDetailed").Range("DetailedFilterCriteria"),
_
CopyToRange:=Sheets("HomeDetailed").Range("DetailedFilterDestination"),
_
Unique:=False

End Sub'

Names are as follows:-
AllDetailedRecords =
"=OFFSET(RecordOfRoundsDetailed!$A$52,0,0,COUNTA(RecordOfRoundsDetailed!$A$52:$A$65536),221)"

DetailedFilterCriteria = "=RecordOfRoundsDetailed!$A$1:$E$2"

DetailedFilterDestination = "=OFFSET(HomeDetailed!$A$52,0,0,1,221)"

When I compile the code it is ok.

Is there any way to establish which part of the code is causing the error or
is there any obvious problem with the code?

Sandy

 

[Sandy]

Update

Ok, I changed my names as follows:-

Names:-

AllRecordsDetailed:-
"=OFFSET(RecordOfRoundsDetailed!$A$52,0,0,COUNTA(RecordOfRoundsDetailed!$A$52:$A$65536),221)"

FilterCriteriaDetailed:-
"=RecordOfRoundsDetailed!$A$1:$E$2"

FilterDestinationDetailed:-
"=OFFSET(HomeDetailed!$A$52,0,0,1,221)

and changed the code as follows (note this involved only changing the
"Names":-

Sub filter()

Sheets("RecordOfRoundsDetailed").Range("AllRecordsDetailed"). _
AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("RecordOfRoundsDetailed").Range("FilterCriteriaDetailed"),
_
CopyToRange:=Sheets("HomeDetailed").Range("FilterDestinationDetailed"),
_
Unique:=False

End Sub

Result - It now works as it should! I am curious though - could the problem
have been the names I was using??

Sandy

 

[Ken Johnson]

If COUNTA(RecordOfRoundsDetailed!$A$52:$A$65536) evaluates to zero
that is the error you will receive.

Ken Johnson

 

[Sandy]

It never evaluates to zero; before the filter is applied a record is
inserted.

I have a feeling that it was just a bit of corruption somewhere that was
corrected when I deleted the old names and then defined the new ones.

Sandy

 

[Dave Peterson]

Maybe you could add a few lines like:

msgbox Sheets("RecordOfRoundsDetailed").Range("AllDetailedRecords") _
.address(external:=true)

To see if everything looks ok.

 

[Sandy]

Excellent suggestion Dave and having added the lines in I am happy that my
code is now doing as it should. Sadly I had already deleted all the original
Names so couldn't use your technique on that - useful for future use though.

Sandy