AdvancedFilter VBA

  • Thread starter Thread starter Sandy
  • Start date Start date
S

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
 
Hi Sandy

Try
Sheets("HomeCourse").Select
Range("HomeRecordsAll").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range("A1:E2"), Unique:=False
 
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:
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.
 
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.
 
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
 
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
 
Hi Roger

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

Thank you.
Sandy
 
Back
Top