Find or lookup function?

[richardff]

A translation of my earlier enquiry - which is probably baffling most people!

I want my macro to copy a row from worksheet A and paste it into a row in worksheet B.

The problem is that the rows in Worksheet B change daily - so I need to instruct the macro to find the appropriate row in Worksheet B first to paste the data in.

Worksheet B contains people's names all the way down column A. All I want my macro to do is "find Joe Blogg's name in column A and paste the data in cells C:E of his row (i.e. a couple of cells to the right of joe blogg's name).

Can anyone help?

[willlobb]

whats in the row that you want to copy?
is it the whole row you want to copy?

[ This Message was edited by: willlobb on 2002-04-04 04:27 ]

[richardff]

The data that is to be pasted is just numbers - the first part of my code which does the copying is as follows:

Sub transfer_data()
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:My DocumentsClients.xls”
Range("C129:G129").Select
Selection.Copy
ActiveWorkbook.Save
ActiveWorkbook.Close


Now, I want to switch to another Workbook and paste this data in cells C:G in the row that has Joe Bloggs name in Column A. Today Joe Bloggs' name happens to be in A9 but tomorrow, after I've inserted other names, it will be on a diferent row.

I've searched the Mr Excel Noticeboard and and I've seen this code which I've been playing around with because it seems like it might be what I'm looking for - but I can't get it to work:

Columns("A:A").Find(What:=Joe Bloggs, After:=ActiveCell, LookIn:=x1Values, LookAt:=x1Whole, SearchOrder:=x1ByColumns, SearchDirection:=x1Next, MatchCase:=False).Active
ActiveCell.Offset(0,3).Select
ActiveSheet.Paste

Hope you can help,

Thanks

[SamS]

Would something like this do for you

Sub FindName()
Dim ThisRow
(this part I presume is where you have already selected your range to copy)
Sheets("Sheet1").Select
Range("C4:E4").Select
(this is the part that copies your selection to the next sheet)
Sheets("Sheet2").Select
Columns("A:A").Select
Selection.Find(What:="Joe Bloggs", After:=ActiveCell, LookIn:=x1Values, _
LookAt:=x1Whole, SearchOrder:=x1ByColumns, SearchDirection:=x1Next, _
MatchCase:=False).Active
ThisRow = ActiveCell.Row
Range("C" & ThisRow).Select
ActiveSheet.Paste
End Sub

Is this what you were looking for?

[SamS]

Richard - your reply came in while I was posting mine - the second half still applies. What I did was place the active cell row number into a variable called This Row and then used it for pasting the data.

[richardff]

Hi Sam,

Thanks for that - this works great but with one small problem: the cell gets located correctly but the data wont paste - is this because it has been dropped from the memory after closing the first worksheet?

[willlobb]

thats exactly what happened, because i wrote a similar piece of code for you and thats what hqppened to me.

[richardff]

Thanks for your help guys - I have to say that this site has helped me on many ocassions and I think it's the most useful resource on the net - really appreciate it!