Find or lookup function?

richardff

New Member
Joined
Apr 3, 2002
Messages
8
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?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
thats exactly what happened, because i wrote a similar piece of code for you and thats what hqppened to me.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top