Sort Data Automatically

[sunilkeswani]

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

 

[Tom Ogilvy]

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

 

[sunilkeswani]

Thanks for the quick response.

I get an error on this line

Set rng = ActiveSheet.Range("A1").CurrentRegion

Could you please advise?

Regards
Sunil

 

[Tom Ogilvy]

rng needs to hold a reference to your database. that is my best guess on
how to get it and it worked fine in my tests. the database was on the
activesheet when I ran the code and the first header was in A1.

You gave no information on where your database is located, so I don't have
additional advice at this time.

 

[sunilkeswani]

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

 

[Tom Ogilvy]

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