On 14 Jun., 09:55, AndreasHermle
wrote:
Dear Experts:

Below macro ...
(1)... creates a user-defined search dialog box and ...
(2)... searches for data records that are listed in a
'xlsheetveryhidden'-worksheet (Sheet name = list)... and
(3)... if found, the data record is copied into the active cell of the
current worksheet.

I would like to have this macro rewritten so that ...
... Excel copies the first instance of a found value/data record right
into row 1 of the current worksheet. Subsequent hits should then be
copied into row 2, row 3 etc. and so forth no matter which cell is
activated.

Is this possible? Help is very much appreciated. Thank you very much
in advance.

Regards, Andreas

Sub Find_it_and_copy_it()
* *Dim varWhat
* *Dim rngfound As Range
* *varWhat = Application.InputBox("Enter text to find")
* *Set rngfound = Sheets("list").Cells.Find(what:=varWhat,
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
* *If Not rngfound Is Nothing Then
* * * rngfound.EntireRow.Copy Cells(ActiveCell.Row, 1)
* *End If
End Sub

This should do it:

Sub Find_it_and_copy_it()
Dim varWhat
Dim rngFound As Range
Dim R As Long
Dim fFound As Range

varWhat = Application.InputBox("Enter text to find")
Set rngFound = Sheets("list").Cells.Find(what:=varWhat, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
Set fFound = rngFound
If Not rngFound Is Nothing Then
Do
R = R + 1
rngFound.EntireRow.Copy Cells(R, 1)
Set rngFound = Sheets("list").Cells.FindNext(after:=rngFound)
Loop Until rngFound.Address = fFound.Address
End If
End Sub