Advanced filter with naming range

[Catherine]

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

 

[NoodNutt]

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.