Advanced Filter Copies in one Case but Fails in the Other?

[FuzzyTom]

Hi,

I have an advance filter with criteria as follows:

Range(ClientRange).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range(ClntName), CopyToRange:=Range("AA2"), unique:=True


and it works just fine ... it copies the title and the data.

But in the next case I have no criteria as follows:

Range("F2").Select
Lastrow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
SiteRange = "F2:" & "F" & Lastrow
Range(SiteRange).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AB2"), unique:=True
Range("AB2").Select
Lastrow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
SiteData = "AB2:" & "AB" & Lastrow

It only copies the title, which comes from F2, and puts it on AB2. If I check SiteRange it shows a range(F2:F518), so there is data there to copy.
On the other hand if you check SiteData it only shows a Range(AB2:AB2)

I must be missing something ... can anyone see it?

Thanks for any help.

 

[FuzzyTom]

Okay ... it looks like the problem might be beyond this point in the program ... another guy ran this code and he could not reproduce the error I am reporting since it worked just fine in both cases.

I will check through the rest of the code to see what it is that is causing a problem.

Thanks for thinking about it though.

 

[FuzzyTom]

I have cut down the program to show the Advanced filters only and to illustrate how they seem to work selectively. I know it is my programming but hopefully it will make it easier to see the problem. Code follows:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">
Option Explicit

Sub Scheduling()
Dim NewSheet As Worksheet, NameSheet As Worksheet, ws As Worksheet
Dim cell AsObject
Dim SheetRange AsString
Dim ClientRange AsString
Dim ClntCrit AsString
Dim ClntName AsString
Dim ClientCcs AsString
Dim SiteRange AsString
Dim StaffRange AsString
Dim CodeRange AsString
Dim Lastrow AsLong

ActiveWorkbook.Sheets("Schedules").Activate
Set NameSheet = Sheets("Schedules")

Application.ScreenUpdating =False

Range("$BE$2").Select
Selection.Value ="Client Name"
Range("$BE$3").Select
Selection.Value ="*-ccs*"
Range("$BF$2").Select
Selection.Value ="Client Name"
Range("$BF$3").Select
Selection.Value ="<>*-ccs*"
Range("$BG$2").Select
Selection.Value ="Client Name"
Range("B2").Select
Lastrow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
ClientRange ="B2:"&"B"& Lastrow
Range("F2").Select
Lastrow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
SiteRange ="F2:"&"F"& Lastrow
Range("G2").Select
Lastrow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
StaffRange ="G2:"&"G"& Lastrow
Range("H2").Select
Lastrow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
CodeRange ="H2:"&"H"& Lastrow
Range("$BF$2").Select
ClntName ="$BF$2:$BF$3"
Range("$BE$2").Select
ClntCrit ="$BE$2:$BE$3"
Range(ClientRange).AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range(ClntCrit), CopyToRange:=Range("$BG$2"),
unique:=True
Range(ClientRange).AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range(ClntName), CopyToRange:=Range("AA2"),
unique:=True
Range(SiteRange).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("AB2"), unique:=True
Range(StaffRange).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("AC2"), unique:=True
Range(CodeRange).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("AD2"), unique:=True
EndSub
</code>
The first two Advanced filter with criteria work just fine. The next three Advanced filters return the Titles only.
Hopefully someone will see where the conflict is in this arrangement since it should work.

 

[FuzzyTom]

I think I found the solution ... later today I will test it on the completed program ... but for now this is what works:

I moved the Advanced Filters without criteria above the Advanced Filters with criteria, in other words the Advanced Filters without Criteria ran first and as a result all filters copied the appropriate ranges to there respective ranges without any problems.

I will have to read up on these Advanced Filters and most probably I will find that they need to run in a specific order.