Excel

[Paul Rimmer]

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]

Its possible with formula, however a formula cannot "copy" any thing i
simply displays results, that said it can be done with a series o
VLOOKUP's perhaps, however if you do not have unique names vlookup wil

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

For Example

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

Thanks in advance for the assistanc

Pau

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[Paul Rimmer]

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]

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

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






Office Discussion' (http://www.thecodecage.com)

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[Paul Rimmer]

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]

This should do what you wan

Code
-------------------
Sub find_and_copy(
Dim IB As String, fndRng As Rang
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 nam
'Sheets("Sheet1").Range(fndRng.Address).EntireRow.Delete shift:=xlU
End Su
-------------------

They are to be copied onto the on sheet. It's an Time in motio
spreadshee
for a few departments where I work, & they want to keep the detail
seperat
for the department, so What I would like to do is find the staff i
tha
perticular department & move the details over for the management. I ca
the
use the same process for other area's






Office Discussion' (http://www.thecodecage.com)

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)