I want a sample macro

[TheMaskEditor]

Friends,
I have a excel worksheet in which i have entered a lot of addresses of my friends, relatives, clients etc.

Each address is fed in each single row and each column is a field like name, address, city, state, phone etc.

Now my problem is, I want a macro to sort my addresses city wise and copy all the addresses for a particular city in a new excel workbook (new excel file)

please help me

 

[NateO]

Here's a sample, this uses multiple searches in one run via a hard coded array:
<pre>
myVal = [{"test",12,3}] 'Add Search Terms Here, text in quotes</pre>

You may want to either subsititute the listed values, or replace this with an input box of some sort.

http://216.92.17.166/board/viewtopic.php?topic=13175&forum=2

 

[TheMaskEditor]

I tried the solution provided by u but i Get the error "Subscript out of range"

give me ur email so i can send u the sample excel file

my email is TheMaskEditor@hotmail.com

 

[NateO]

Before we switch to e-mail, try the following which is a tinkered version of the original that I linked to:<pre>
Sub Search_Sht()
'Macro recorded by Nate
Dim sht As Worksheet, act As Range, act2 As Range, srcBk As Workbook
Dim o As Long, frst As Range, cnt As Integer
Dim myVal, myWkbk As Workbook, mySht As Integer, sRc As String
'change a65536 to an appropriate column
myVal = Application.InputBox("Please Enter City Name", Type:=2)
If myVal = False Then Exit Sub
Set srcBk = ThisWorkbook
sRc = ThisWorkbook.ActiveSheet.Name
Set myWkbk = Workbooks.Add 'Change the target Workbook name here
mySht = 1 'Change the target sheet index here
o = myWkbk.sheets(mySht).[a65536].End(xlUp)(2).Row
Set act = ThisWorkbook.sheets(sRc).Cells.Find(What:=myVal, _
after:=[iv65536], LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not act Is Nothing Then
act.EntireRow.Copy myWkbk.sheets(mySht).Rows(o)
myWkbk.sheets(mySht).Cells(o, 1).AddComment _
Text:="Result Location: " & _
sheets(sRc).Name & "!" & act.Address(False, False)
o = o + 1
Set frst = act
again:
Set act2 = ThisWorkbook.sheets(sRc).Cells.FindNext(act)
If Not act2 Is Nothing Then
If act2.Address<> act.Address _
And act2.Address<> frst.Address Then
act2.EntireRow.Copy _
myWkbk.sheets(mySht).Rows(o)
myWkbk.sheets(mySht).Cells(o, 1).AddComment _
Text:="Result Location: " & _
sheets(sRc).Name & "!" & act2.Address(False, False)
o = o + 1
Set act = act2
GoTo again
End If
End If
End If
End Sub</pre><pre></pre>

Hopefully this will perform as expected. Enter the city name, and then do a quick sort on your new workbook.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-10-16 15:02

 

[TheMaskEditor]

Thank you very much you macro did the work for me.

Thank you.

TME2002

 

[NateO]

You're welcome TME2002. Incidentally, welcome to the board!

C'mon back and bring all your friends.