Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced filter with naming range
Hi,
I need to copy all the data from SheetB with ENV_342 criteria to SheetA. I am using the advanced filter. In this code I am using the range A5:B911, but I need to replace this by FILTER naming range. If I just replace the text A5:B911 with FILTER I received the execute error message 1004. Do anyone have a solution? I am using Excel 2003. This is my actual code Sheets("Sheet B").Range("A5:B911").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A3"), Unique:=False Range("A4").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="ENV_342", RefersTo:=Selection Thanks Catherine |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced filter with naming range
G'day Catherine
You may find this link helpful http://www.rondebruin.nl/copy2.htm here is a copy of my code that I modified to suite my purposes based on Ron's Code. Sub Split_Data() Dim SourceSheet As Worksheet Dim DestinationSheet As Worksheet Dim rng As Range Dim rng2 As Range With Application .ScreenUpdating = False .EnableEvents = False End With Set SourceSheet = Sheets("Data") Set rng = SourceSheet.Range("A8:M" & Rows.Count) Set DestinationSheet = Sheets("NSW") SourceSheet.AutoFilterMode = False rng.AutoFilter Field:=1, Criteria1:="=SYD" SourceSheet.AutoFilter.Range.Copy With DestinationSheet.Range("A5") .PasteSpecial xlPasteValues Application.CutCopyMode = False .Select End With End Sub This peice of code copies only rows that match "Syd" in the first column and copies it to my new sheet NSW, you can also create a new sheet. HTH Mark. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced filter with naming range
http://www.rondebruin.nl/copy2.htm
I think Mark wanted to post this link http://www.rondebruin.nl/copy5.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "NoodNutt" wrote in message ... G'day Catherine You may find this link helpful http://www.rondebruin.nl/copy2.htm here is a copy of my code that I modified to suite my purposes based on Ron's Code. Sub Split_Data() Dim SourceSheet As Worksheet Dim DestinationSheet As Worksheet Dim rng As Range Dim rng2 As Range With Application .ScreenUpdating = False .EnableEvents = False End With Set SourceSheet = Sheets("Data") Set rng = SourceSheet.Range("A8:M" & Rows.Count) Set DestinationSheet = Sheets("NSW") SourceSheet.AutoFilterMode = False rng.AutoFilter Field:=1, Criteria1:="=SYD" SourceSheet.AutoFilter.Range.Copy With DestinationSheet.Range("A5") .PasteSpecial xlPasteValues Application.CutCopyMode = False .Select End With End Sub This peice of code copies only rows that match "Syd" in the first column and copies it to my new sheet NSW, you can also create a new sheet. HTH Mark. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using advanced filter in VBA with a dynamic range | Excel Programming | |||
Advanced Filter to Dynamic Range | Excel Programming | |||
Automatic Range Naming using Advanced Filter? | Excel Programming | |||
advanced filter a range | Excel Worksheet Functions | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions |