I have a named range (dynamic) called 'HomeRecordsAll' which refers to a
range currently ("A1:GL28") on Sheets("HomeCourse").

The name refers to :-
'=OFFSET(HomeCourse!$A$9,0,0,COUNT(RecordOfRounds!$A$52:$A$10000)+1,194)'.

I am attempting to incorporate the 'HomeRecordsAll' into code for an
advanced filter.
The following does not work:-

Sheets("HomeCourse").Select
Range("HomeRecordsAll").AdvancedFilter Action:=xlFilterInPlace, _
Criteria:=Range("A1:E2"), Unique:=False

Any help appreciated.
Thanks
Sandy

Oops - the range in the first line should read ("A9:GL28")
Sandy

"Sandy" <(E-Mail Removed)> wrote in message
news:6D9169BC-49EF-485C-9630-(E-Mail Removed)...
> I have a named range (dynamic) called 'HomeRecordsAll' which refers to a
> range currently ("A1:GL28") on Sheets("HomeCourse").
>
> The name refers to :-
>
> '=OFFSET(HomeCourse!$A$9,0,0,COUNT(RecordOfRounds!$A$52:$A$10000)+1,194)'.
>
> I am attempting to incorporate the 'HomeRecordsAll' into code for an
> advanced filter.
> The following does not work:-
>
> Sheets("HomeCourse").Select
> Range("HomeRecordsAll").AdvancedFilter Action:=xlFilterInPlace, _
> Criteria:=Range("A1:E2"), Unique:=False
>
> Any help appreciated.
> Thanks
> Sandy

Hi Sandy

Try
Sheets("HomeCourse").Select
Range("HomeRecordsAll").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range("A1:E2"), Unique:=False


--
Regards
Roger Govier

"Sandy" <(E-Mail Removed)> wrote in message
news:6D9169BC-49EF-485C-9630-(E-Mail Removed)...
> I have a named range (dynamic) called 'HomeRecordsAll' which refers to a
> range currently ("A1:GL28") on Sheets("HomeCourse").
>
> The name refers to :-
>
> '=OFFSET(HomeCourse!$A$9,0,0,COUNT(RecordOfRounds!$A$52:$A$10000)+1,194)'.
>
> I am attempting to incorporate the 'HomeRecordsAll' into code for an
> advanced filter.
> The following does not work:-
>
> Sheets("HomeCourse").Select
> Range("HomeRecordsAll").AdvancedFilter Action:=xlFilterInPlace, _
> Criteria:=Range("A1:E2"), Unique:=False
>
> Any help appreciated.
> Thanks
> Sandy

From any sheet in the workbook....perhaps this:

With Sheets("HomeCourse")
Range("HomeRecordsAll").AdvancedFilter _
Action:=xlFilterInPlace, _
Criteriarange:=.Range("A1:E2"), _
Unique:=False
End With

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Sandy" <(E-Mail Removed)> wrote in message
news:6D9169BC-49EF-485C-9630-(E-Mail Removed)...
>I have a named range (dynamic) called 'HomeRecordsAll' which refers to a
>range currently ("A1:GL28") on Sheets("HomeCourse").
>
> The name refers to :-
>
> '=OFFSET(HomeCourse!$A$9,0,0,COUNT(RecordOfRounds!$A$52:$A$10000)+1,194)'.
>
> I am attempting to incorporate the 'HomeRecordsAll' into code for an
> advanced filter.
> The following does not work:-
>
> Sheets("HomeCourse").Select
> Range("HomeRecordsAll").AdvancedFilter Action:=xlFilterInPlace, _
> Criteria:=Range("A1:E2"), Unique:=False
>
> Any help appreciated.
> Thanks
> Sandy

Sandy:
That should work, but make sure each column in your "HomeRecordsAll" range
has a column Heading
and within your criteria range (A1:E2) each cell in row 1 has one of your
194 column headings.


"Sandy" <(E-Mail Removed)> wrote in message
news:5AF39E4C-D321-4CE8-B133-(E-Mail Removed)...
> Oops - the range in the first line should read ("A9:GL28")
> Sandy
>
> "Sandy" <(E-Mail Removed)> wrote in message
> news:6D9169BC-49EF-485C-9630-(E-Mail Removed)...
>> I have a named range (dynamic) called 'HomeRecordsAll' which refers to a
>> range currently ("A1:GL28") on Sheets("HomeCourse").
>>
>> The name refers to :-
>>
>> '=OFFSET(HomeCourse!$A$9,0,0,COUNT(RecordOfRounds!$A$52:$A$10000)+1,194)'.
>>
>> I am attempting to incorporate the 'HomeRecordsAll' into code for an
>> advanced filter.
>> The following does not work:-
>>
>> Sheets("HomeCourse").Select
>> Range("HomeRecordsAll").AdvancedFilter Action:=xlFilterInPlace, _
>> Criteria:=Range("A1:E2"), Unique:=False
>>
>> Any help appreciated.
>> Thanks
>> Sandy
>

I notice that you goto a lot of trouble to safeguard your email address and
yet attach unwelcome workbooks to a message to a newsgroup who does not want
them. If someone wants to see your workbook they will offer for you to send
directly to them.
http://www.cpearson.com/excel/newposte.htm

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Sandy" <(E-Mail Removed)> wrote in message
news:BE7518D4-AD74-418C-AAB3-(E-Mail Removed)...
> Hi Guys,
>
> I have tried all of your suggestions - no avail.
>
> Still getting "Runtime error 1004 - Application-defined or Object-defined
> error".
>
> I have attached my workbook, maybe that will help in analysing the
> problem.
> The sub in question is attached to Sheet ("RecordOfRounds")
>
> Thanks
> Sandy
>
> "Sandy" <(E-Mail Removed)> wrote in message
> news:5AF39E4C-D321-4CE8-B133-(E-Mail Removed)...
>> Oops - the range in the first line should read ("A9:GL28")
>> Sandy
>>
>> "Sandy" <(E-Mail Removed)> wrote in message
>> news:6D9169BC-49EF-485C-9630-(E-Mail Removed)...
>>> I have a named range (dynamic) called 'HomeRecordsAll' which refers to a
>>> range currently ("A1:GL28") on Sheets("HomeCourse").
>>>
>>> The name refers to :-
>>>
>>> '=OFFSET(HomeCourse!$A$9,0,0,COUNT(RecordOfRounds!$A$52:$A$10000)+1,194)'.
>>>
>>> I am attempting to incorporate the 'HomeRecordsAll' into code for an
>>> advanced filter.
>>> The following does not work:-
>>>
>>> Sheets("HomeCourse").Select
>>> Range("HomeRecordsAll").AdvancedFilter Action:=xlFilterInPlace, _
>>> Criteria:=Range("A1:E2"), Unique:=False
>>>
>>> Any help appreciated.
>>> Thanks
>>> Sandy
>>

Don

My apologies - I did not realise it was taboo.
I consider myself reprimanded.

Sandy

"Don Guillett" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> I notice that you goto a lot of trouble to safeguard your email address
> and yet attach unwelcome workbooks to a message to a newsgroup who does
> not want them. If someone wants to see your workbook they will offer for
> you to send directly to them.
> http://www.cpearson.com/excel/newposte.htm
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Sandy" <(E-Mail Removed)> wrote in message
> news:BE7518D4-AD74-418C-AAB3-(E-Mail Removed)...
>> Hi Guys,
>>
>> I have tried all of your suggestions - no avail.
>>
>> Still getting "Runtime error 1004 - Application-defined or Object-defined
>> error".
>>
>> I have attached my workbook, maybe that will help in analysing the
>> problem.
>> The sub in question is attached to Sheet ("RecordOfRounds")
>>
>> Thanks
>> Sandy
>>
>> "Sandy" <(E-Mail Removed)> wrote in message
>> news:5AF39E4C-D321-4CE8-B133-(E-Mail Removed)...
>>> Oops - the range in the first line should read ("A9:GL28")
>>> Sandy
>>>
>>> "Sandy" <(E-Mail Removed)> wrote in message
>>> news:6D9169BC-49EF-485C-9630-(E-Mail Removed)...
>>>> I have a named range (dynamic) called 'HomeRecordsAll' which refers to
>>>> a
>>>> range currently ("A1:GL28") on Sheets("HomeCourse").
>>>>
>>>> The name refers to :-
>>>>
>>>> '=OFFSET(HomeCourse!$A$9,0,0,COUNT(RecordOfRounds!$A$52:$A$10000)+1,194)'.
>>>>
>>>> I am attempting to incorporate the 'HomeRecordsAll' into code for an
>>>> advanced filter.
>>>> The following does not work:-
>>>>
>>>> Sheets("HomeCourse").Select
>>>> Range("HomeRecordsAll").AdvancedFilter Action:=xlFilterInPlace,
>>>> _
>>>> Criteria:=Range("A1:E2"), Unique:=False
>>>>
>>>> Any help appreciated.
>>>> Thanks
>>>> Sandy
>>>
>

Hi Sandy

Whilst I agree with what Don said, and you have acknowledged the fact, I
have on this occasion opened your workbook.
Your problem is that you have no headers to your data on Records of Rounds.
Advanced Filter cannot work without headers.

--
Regards
Roger Govier

"Sandy" <(E-Mail Removed)> wrote in message
news:BE7518D4-AD74-418C-AAB3-(E-Mail Removed)...
> Hi Guys,
>
> I have tried all of your suggestions - no avail.
>
> Still getting "Runtime error 1004 - Application-defined or Object-defined
> error".
>
> I have attached my workbook, maybe that will help in analysing the
> problem.
> The sub in question is attached to Sheet ("RecordOfRounds")
>
> Thanks
> Sandy
>
> "Sandy" <(E-Mail Removed)> wrote in message
> news:5AF39E4C-D321-4CE8-B133-(E-Mail Removed)...
>> Oops - the range in the first line should read ("A9:GL28")
>> Sandy
>>
>> "Sandy" <(E-Mail Removed)> wrote in message
>> news:6D9169BC-49EF-485C-9630-(E-Mail Removed)...
>>> I have a named range (dynamic) called 'HomeRecordsAll' which refers to a
>>> range currently ("A1:GL28") on Sheets("HomeCourse").
>>>
>>> The name refers to :-
>>>
>>> '=OFFSET(HomeCourse!$A$9,0,0,COUNT(RecordOfRounds!$A$52:$A$10000)+1,194)'.
>>>
>>> I am attempting to incorporate the 'HomeRecordsAll' into code for an
>>> advanced filter.
>>> The following does not work:-
>>>
>>> Sheets("HomeCourse").Select
>>> Range("HomeRecordsAll").AdvancedFilter Action:=xlFilterInPlace, _
>>> Criteria:=Range("A1:E2"), Unique:=False
>>>
>>> Any help appreciated.
>>> Thanks
>>> Sandy
>>

Roger, and all

I tried headers on the RecordOfRounds sheet again to no avail - however I
wondered if the fact that the Sub was on RecordOfRounds and the
AdvancedFilter was on HomeCourse, if this had a bearing so without any
further adjustment to my workbook other than move my code to a module, it
now operates perfectly.

Not sure why, but......

Thanks again
Sandy

"Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message
news:1F54A704-D68C-4491-98E0-(E-Mail Removed)...
> Hi Sandy
>
> Whilst I agree with what Don said, and you have acknowledged the fact, I
> have on this occasion opened your workbook.
> Your problem is that you have no headers to your data on Records of
> Rounds.
> Advanced Filter cannot work without headers.
>
> --
> Regards
> Roger Govier
>
> "Sandy" <(E-Mail Removed)> wrote in message
> news:BE7518D4-AD74-418C-AAB3-(E-Mail Removed)...
>> Hi Guys,
>>
>> I have tried all of your suggestions - no avail.
>>
>> Still getting "Runtime error 1004 - Application-defined or Object-defined
>> error".
>>
>> I have attached my workbook, maybe that will help in analysing the
>> problem.
>> The sub in question is attached to Sheet ("RecordOfRounds")
>>
>> Thanks
>> Sandy
>>
>> "Sandy" <(E-Mail Removed)> wrote in message
>> news:5AF39E4C-D321-4CE8-B133-(E-Mail Removed)...
>>> Oops - the range in the first line should read ("A9:GL28")
>>> Sandy
>>>
>>> "Sandy" <(E-Mail Removed)> wrote in message
>>> news:6D9169BC-49EF-485C-9630-(E-Mail Removed)...
>>>> I have a named range (dynamic) called 'HomeRecordsAll' which refers to
>>>> a
>>>> range currently ("A1:GL28") on Sheets("HomeCourse").
>>>>
>>>> The name refers to :-
>>>>
>>>> '=OFFSET(HomeCourse!$A$9,0,0,COUNT(RecordOfRounds!$A$52:$A$10000)+1,194)'.
>>>>
>>>> I am attempting to incorporate the 'HomeRecordsAll' into code for an
>>>> advanced filter.
>>>> The following does not work:-
>>>>
>>>> Sheets("HomeCourse").Select
>>>> Range("HomeRecordsAll").AdvancedFilter Action:=xlFilterInPlace,
>>>> _
>>>> Criteria:=Range("A1:E2"), Unique:=False
>>>>
>>>> Any help appreciated.
>>>> Thanks
>>>> Sandy
>>>

Hi Sandy

No the code wouldn't work form a Sheet, it does need to be in an inserted
module.
I hadn't bothered to look for any code in your workbook, when I saw that
there were no modules. I thought you had just sent the sheet with any
macros, so people would have less fear of opening them.
I could see you didn't have a header row for your data - hence my response.

Your code (different to the question you posted) only works without headers
because you are first copying all of the data from the Source
(RecordsofRounds) to the Destination (Homecourse).
With only 19 rows of data that isn't too much of an overhead, but with a
large data set it would be different.
That is not necessary - you can filter the records directly.

here is what I did.
I cut columns GH:GL on the source sheet, and inserted at column A.
I named Row 52 of the new columns A:E with the headings Tournament, Course,
Date, Weather Conditions am , Weather Conditions pm
(I assumed that the contents of the cells ere referring to am and pm
Conditions on the same day)
I named the remaining columns of Row 52 with their cell address, just to
have a set of unique field names.

I put the same headings from A52:E52 in A1:E1 of the destination sheet.

I amended your range Allrecords to
=OFFSET(RecordOfRounds!$A$52,0,0,COUNTA(RecordOfRounds!$A$52:$A$65536),194)
I created a Named range of Criteria as =HomeCourse!$A$1:$E$2
I created a named range of Destination as =OFFSET(HomeCourse!$A$9,0,0,1,194)

The code then simplifies to

Sub SaveRecord2()

Application.ScreenUpdating = False
Application.EnableEvents = False

Sheets("HomeCourse").Range("$9:$65536").ClearContents

Sheets("RecordOfRounds").Range("AllRecords"). _
AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("HomeCourse").Range("Criteria"), _
CopyToRange:=Sheets("HomeCourse").Range("Destination"), _
Unique:=False

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

--
Regards
Roger Govier

"Sandy" <(E-Mail Removed)> wrote in message
news:989F6937-A389-478D-AC0F-(E-Mail Removed)...
> Roger, and all
>
> I tried headers on the RecordOfRounds sheet again to no avail - however I
> wondered if the fact that the Sub was on RecordOfRounds and the
> AdvancedFilter was on HomeCourse, if this had a bearing so without any
> further adjustment to my workbook other than move my code to a module, it
> now operates perfectly.
>
> Not sure why, but......
>
> Thanks again
> Sandy
>
> "Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message
> news:1F54A704-D68C-4491-98E0-(E-Mail Removed)...
>> Hi Sandy
>>
>> Whilst I agree with what Don said, and you have acknowledged the fact, I
>> have on this occasion opened your workbook.
>> Your problem is that you have no headers to your data on Records of
>> Rounds.
>> Advanced Filter cannot work without headers.
>>
>> --
>> Regards
>> Roger Govier
>>
>> "Sandy" <(E-Mail Removed)> wrote in message
>> news:BE7518D4-AD74-418C-AAB3-(E-Mail Removed)...
>>> Hi Guys,
>>>
>>> I have tried all of your suggestions - no avail.
>>>
>>> Still getting "Runtime error 1004 - Application-defined or
>>> Object-defined
>>> error".
>>>
>>> I have attached my workbook, maybe that will help in analysing the
>>> problem.
>>> The sub in question is attached to Sheet ("RecordOfRounds")
>>>
>>> Thanks
>>> Sandy
>>>
>>> "Sandy" <(E-Mail Removed)> wrote in message
>>> news:5AF39E4C-D321-4CE8-B133-(E-Mail Removed)...
>>>> Oops - the range in the first line should read ("A9:GL28")
>>>> Sandy
>>>>
>>>> "Sandy" <(E-Mail Removed)> wrote in message
>>>> news:6D9169BC-49EF-485C-9630-(E-Mail Removed)...
>>>>> I have a named range (dynamic) called 'HomeRecordsAll' which refers to
>>>>> a
>>>>> range currently ("A1:GL28") on Sheets("HomeCourse").
>>>>>
>>>>> The name refers to :-
>>>>>
>>>>> '=OFFSET(HomeCourse!$A$9,0,0,COUNT(RecordOfRounds!$A$52:$A$10000)+1,194)'.
>>>>>
>>>>> I am attempting to incorporate the 'HomeRecordsAll' into code for an
>>>>> advanced filter.
>>>>> The following does not work:-
>>>>>
>>>>> Sheets("HomeCourse").Select
>>>>> Range("HomeRecordsAll").AdvancedFilter Action:=xlFilterInPlace,
>>>>> _
>>>>> Criteria:=Range("A1:E2"), Unique:=False
>>>>>
>>>>> Any help appreciated.
>>>>> Thanks
>>>>> Sandy
>>>>