Advanced filter in VBA
 

I have a sheet called database where I filter the data in using advanced
filter.
I have a sheet called criteria that has the criteria range and the
extraction range.
1. I used codes to find my used range in the database = filterRange
2. I define my criteria range =CriteriaRange
3. I define my extraction range = ExtractionRange
4 I apply the Advanced filter codes to the ranges.

Please could look over the codes, it is not working.

Sub DailyCowList()

' Show the database used range

Worksheets("database").Activate

DmyLastCell = LastCell(Worksheets("Database")).Address
DmyRange = "a4:" & DmyLastCell
Application.ScreenUpdating = True
Range(DmyRange).Select

'to verify the range
MsgBox DmyRange
MsgBox DmyLastCell
LimitRange = "A25:" & DmyLastCell

Set FilterRange = Worksheets("Database").Range(DmyRange)

Worksheets("Criteria").Activate
With Worksheets("Criteria")
Set CriteriaRange = .Range("A2:U22")
Set ExtractionRange = .Range(LimitRange)

End With

' Advanced filter

FilterRange.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=CriteriaRange, _
CopyToRange:=ExtractionRange

Thank you for your help.


--
mayou
------------------------------------------------------------------------
mayou's Profile: http://www.excelforum.com/member.php...o&userid=33359
View this thread: http://www.excelforum.com/showthread...hreadid=537196

Advanced filter in VBA
 

I would suggest changing

Set ExtractionRange = .Range(LimitRange)

to just

Set ExtractionRange = .Range("A25")

if you are copying all columns. I did a simple demo program and that worked
fine for me.

Sub copydate()
Dim d As Range, r As Range
Set d = Worksheets(1).Range("A1").CurrentRegion
Set r = d(1).Offset(0, d.Columns.Count + 1)
r.Value = "type"
r(2).Formula = "=""=ws"""
Set dest = Worksheets(2).Range("A25")
d.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=r.Resize(2, 1), _
CopyToRange:=dest, _
Unique:=False
End Sub

--
Regards,
Tom Ogilvy


"mayou" wrote:


I have a sheet called database where I filter the data in using advanced
filter.
I have a sheet called criteria that has the criteria range and the
extraction range.
1. I used codes to find my used range in the database = filterRange
2. I define my criteria range =CriteriaRange
3. I define my extraction range = ExtractionRange
4 I apply the Advanced filter codes to the ranges.

Please could look over the codes, it is not working.

Sub DailyCowList()

' Show the database used range

Worksheets("database").Activate

DmyLastCell = LastCell(Worksheets("Database")).Address
DmyRange = "a4:" & DmyLastCell
Application.ScreenUpdating = True
Range(DmyRange).Select

'to verify the range
MsgBox DmyRange
MsgBox DmyLastCell
LimitRange = "A25:" & DmyLastCell

Set FilterRange = Worksheets("Database").Range(DmyRange)

Worksheets("Criteria").Activate
With Worksheets("Criteria")
Set CriteriaRange = .Range("A2:U22")
Set ExtractionRange = .Range(LimitRange)

End With

' Advanced filter

FilterRange.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=CriteriaRange, _
CopyToRange:=ExtractionRange

Thank you for your help.


--
mayou
------------------------------------------------------------------------
mayou's Profile: http://www.excelforum.com/member.php...o&userid=33359
View this thread: http://www.excelforum.com/showthread...hreadid=537196