Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Filter - filter rows < | Excel Discussion (Misc queries) | |||
Why won't advanced filter return filter results? | Excel Worksheet Functions | |||
How do I use advanced filter to filter for blank cells? | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |