Excel

I'm using Office 2003. I'm trying to search for a name on 1 work sheet & when
this is found, I want to copy this & the following data in the next few
columns onto a seperate work sheet. Is this possible as a formula or will it
have to be VBA??

For Example:

I search for John Smith in column A & when this has been found, the
information in column A to column J needs to be copied on a seperate
worksheet.

Thanks in advance for the assistance

Paul

Its possible with formula, however a formula cannot "copy" any thing it
simply displays results, that said it can be done with a series of
VLOOKUP's perhaps, however if you do not have unique names vlookup will
only give you the first result, are the names going to be unique?Paul Rimmer;422535 Wrote:
I'm using Office 2003. I'm trying to search for a name on 1 work sheet &
when
this is found, I want to copy this & the following data in the next few
columns onto a seperate work sheet. Is this possible as a formula or
will it
have to be VBA??

For Example:

I search for John Smith in column A & when this has been found, the
information in column A to column J needs to be copied on a seperate
worksheet.

Thanks in advance for the assistance

Paul


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117521

Yes, it's going to have some specific details that need to be moved or copied
seperately for data protection. It looks like I'm going to have to do this
through a macro or manually search through all the details.



"Simon Lloyd" wrote:


Its possible with formula, however a formula cannot "copy" any thing it
simply displays results, that said it can be done with a series of
VLOOKUP's perhaps, however if you do not have unique names vlookup will
only give you the first result, are the names going to be unique?Paul Rimmer;422535 Wrote:
I'm using Office 2003. I'm trying to search for a name on 1 work sheet &
when
this is found, I want to copy this & the following data in the next few
columns onto a seperate work sheet. Is this possible as a formula or
will it
have to be VBA??

For Example:

I search for John Smith in column A & when this has been found, the
information in column A to column J needs to be copied on a seperate
worksheet.

Thanks in advance for the assistance

Paul


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117521

Ok well some code to do that is very easy, when you say copy the
columns A to J to a seperate sheet i guess you mean the one row a to j
that ohn Smith was found on to a new sheet, do you want a new sheet for
each item you find or are they all to copied to one specific sheet?

Paul Rimmer;422616 Wrote:
Yes, it's going to have some specific details that need to be moved or
copied
seperately for data protection. It looks like I'm going to have to do
this
through a macro or manually search through all the details.



"Simon Lloyd" wrote:


Its possible with formula, however a formula cannot "copy" any thing
it
simply displays results, that said it can be done with a series of
VLOOKUP's perhaps, however if you do not have unique names vlookup
will
only give you the first result, are the names going to be unique?Paul
Rimmer;422535 Wrote:
I'm using Office 2003. I'm trying to search for a name on 1 work
sheet &
when
this is found, I want to copy this & the following data in the next
few
columns onto a seperate work sheet. Is this possible as a formula
or
will it
have to be VBA??

For Example:

I search for John Smith in column A & when this has been found, the
information in column A to column J needs to be copied on a
seperate
worksheet.

Thanks in advance for the assistance

Paul


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft
Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'Excel - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=117521)




--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117521

They are to be copied onto the on sheet. It's an Time in motion spreadsheet
for a few departments where I work, & they want to keep the details seperate
for the department, so What I would like to do is find the staff in that
perticular department & move the details over for the management. I can then
use the same process for other area's.

"Simon Lloyd" wrote:


Ok well some code to do that is very easy, when you say copy the
columns A to J to a seperate sheet i guess you mean the one row a to j
that ohn Smith was found on to a new sheet, do you want a new sheet for
each item you find or are they all to copied to one specific sheet?

Paul Rimmer;422616 Wrote:
Yes, it's going to have some specific details that need to be moved or
copied
seperately for data protection. It looks like I'm going to have to do
this
through a macro or manually search through all the details.



"Simon Lloyd" wrote:


Its possible with formula, however a formula cannot "copy" any thing
it
simply displays results, that said it can be done with a series of
VLOOKUP's perhaps, however if you do not have unique names vlookup
will
only give you the first result, are the names going to be unique?Paul
Rimmer;422535 Wrote:
I'm using Office 2003. I'm trying to search for a name on 1 work
sheet &
when
this is found, I want to copy this & the following data in the next
few
columns onto a seperate work sheet. Is this possible as a formula
or
will it
have to be VBA??

For Example:

I search for John Smith in column A & when this has been found, the
information in column A to column J needs to be copied on a
seperate
worksheet.

Thanks in advance for the assistance

Paul


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft
Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'Excel - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=117521)




--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117521

This should do what you want

Code:
--------------------
Sub find_and_copy()
Dim IB As String, fndRng As Range
IB = Application.InputBox("Enter the name to be moved", "Name Extraction", "John Smith")
Set fndRng = Sheets("Sheet1").Cells.Find(What:=IB, After:=Sheets("Sheet1").Range("A1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
Sheets("Sheet1").Range("A" & fndRng.Row & ":J" & fndRng.Row).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
'uncomment the next line if you want to delete the original row of the found name
'Sheets("Sheet1").Range(fndRng.Address).EntireRow. Delete shift:=xlUp
End Sub
--------------------



Paul Rimmer;422651 Wrote:
They are to be copied onto the on sheet. It's an Time in motion
spreadsheet
for a few departments where I work, & they want to keep the details
seperate
for the department, so What I would like to do is find the staff in
that
perticular department & move the details over for the management. I can
then
use the same process for other area's.

"Simon Lloyd" wrote:


Ok well some code to do that is very easy, when you say copy the
columns A to J to a seperate sheet i guess you mean the one row a to
j
that ohn Smith was found on to a new sheet, do you want a new sheet
for
each item you find or are they all to copied to one specific sheet?

Paul Rimmer;422616 Wrote:
Yes, it's going to have some specific details that need to be moved
or
copied
seperately for data protection. It looks like I'm going to have to
do
this
through a macro or manually search through all the details.



"Simon Lloyd" wrote:


Its possible with formula, however a formula cannot "copy" any
thing
it
simply displays results, that said it can be done with a series
of
VLOOKUP's perhaps, however if you do not have unique names
vlookup
will
only give you the first result, are the names going to be
unique?Paul
Rimmer;422535 Wrote:
I'm using Office 2003. I'm trying to search for a name on 1
work
sheet &
when
this is found, I want to copy this & the following data in the
next
few
columns onto a seperate work sheet. Is this possible as a
formula
or
will it
have to be VBA??

For Example:

I search for John Smith in column A & when this has been found,
the
information in column A to column J needs to be copied on a
seperate
worksheet.

Thanks in advance for the assistance

Paul


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('The Code Cage - Microsoft Office Help -
Microsoft
Office Discussion' ('The Code Cage - Microsoft Office Help -
Microsoft Office Discussion' (http://www.thecodecage.com)))


------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile:
Simon
Lloyd' ('The Code Cage Forums - View Profile: Simon Lloyd'
(http://www.thecodecage.com/forumz/member.php?userid=1))
View this thread: 'Excel - The Code Cage Forums'
('Excel - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh....php?t=117521))




--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft
Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'Excel - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=117521)




--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117521