AdvancedFilter VBA

[Sandy]

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]

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

 

[Roger Govier]

Hi Sandy

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

 

[Ron Coderre]

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)

 

[DJH]

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.

 

[Don Guillett]

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

 

[Sandy]

Don

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

Sandy

 

[Roger Govier]

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.

 

[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]

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

 

[Sandy]

Hi Roger

Sorry for the delay in replying. The solution you have outlined is perfect.

Thank you.
Sandy