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
' specify the destination sheet here
Set sh = Worksheets("NewData")
' possibly clear that sheet
sh.UsedRange.EntireRow.Delete
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
ups.com...
Thanks Tom,

This works. The only problem is, I would need to run this everyday, and
it creates a new sheet with a new name. What I need is a standard sheet
name, so that I can reference some formulae to it...Could you please
help with this?

Regards
Sunil