I want a sample macro

TheMaskEditor

New Member
Joined
Oct 15, 2002
Messages
3
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
You're welcome TME2002. Incidentally, welcome to the board!

C'mon back and bring all your friends. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,559
Messages
5,596,841
Members
414,107
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top