Find and export

[Jack]

Hi...

Each week I have an excel spreadsheet to work on that
requires me to find speficic values, and the rows on
which they appear need to exported to a new excel work
book (not a sheet).

Ideally I'm looking for a macro button that launches an
input box into which for example I type the word London.
Wherever the word London is found I want the rows on
which London is found to to be exported to a new workbook
in a block (no blank rows) format. Where rows have been
deleted on the original workbook cells are to be moved
upwards.

Can anyone help me with this?

Yours graciously

Gordon.

 

[Bernie Deitrick]

Gordon,

Below is code that will extract your records to a new workbook. The
database is assumed to be the block of cells contiguous with cell A1 of the
activesheet of the activeworkbook, and the extraction is based on the value
you enter when prompted. The extract is based on the values in column C
(the Field:=3 part).

If you need help modifying the code, post back.

HTH,
Bernie
MS Excel MVP

Sub ExtractPartOfDataBase()
Dim myBk1 As Workbook
Dim myBk2 As Workbook

Set myBk1 = ActiveWorkbook
Set myBk2 = Workbooks.Add

With myBk1.ActiveSheet.Range("A1").CurrentRegion
.AutoFilter Field:=3, _
Criteria1:=Application.InputBox("Enter Key Word")
.SpecialCells(xlCellTypeVisible).Copy _
myBk2.Sheets(1).Range("A1")
.AutoFilter
End With

myBk2.SaveAs Application.GetSaveAsFilename _
(, "Excel Files (*.xls), *.xls")
End Sub