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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
 
Upvote 0
You're welcome TME2002. Incidentally, welcome to the board!

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

Forum statistics

Threads
1,219,029
Messages
6,145,869
Members
450,652
Latest member
JR1

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
Back
Top