Sub CopyData()
Dim rng As Range, rng1 As Range, rng2 As Range
Set rng = ActiveSheet.Range("A1").CurrentRegion
Set rng1 = rng.Offset(0, rng.Columns.Count + 2).Resize(1, 1)
rng1.Value = "State"
rng1.Offset(1, 0).Value = "Florida"
With Worksheets
Set sh = .Add(after:=Worksheets(.Count))
End With
Set rng2 = sh.Range("A1").Resize(1, rng.Columns.Count)
rng2.Value = rng.Rows(1).Cells.Value
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=rng1.Resize(2, 1), _
CopyToRange:=rng2, _
Unique:=False
rng1.Resize(2, 1).ClearContents
End Sub

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
Hi,

I have an excel sheet dump with huge amount of data that looks somewhat
like this:

SNo. State City
1 Florida Miami
2 California Los Angeles
3 Georgia Atlanta
4 Florida West Palm Beach
5 Florida Talahasee

How do I write a macro/code that will pull out the following into a
separate sheet?

SNo. State City
1 Florida Miami
4 Florida West Palm Beach
5 Florida Talahasee

i.e I want the sheet to filter out all rows with "Florida" in the State
Column.

PLEASE HELP !!

I need this urgently!!

Regards
Sunil