Finding and copying to a new worksheet (Excel)

[Chelsea7]

Good day,

I would like to do the following using a macro in Excel.

Read the values in column "A", find the matching values in column "E". Copy columns E -F then paste it on another sheet. For example;
Under column "A", 10032, find this value under column "E" then copy column ranges E-F and paste on another worksheet. That's all, no sorting. I was trying to use a loop routing with the Find command but it doesn't do what I want.


A B E
10032 11 Hull Ave 10028 5 Renner St
10028 3 Palmer Dr 10032 2 Hutchinson

ANOTHER WORKSHEET
A B
10032 2 Hutchinson

Your assistance will be greatly appreciated.

 

[SkipVought]

Hi,

Please post table examples using the PRE /PRE TGML markup, like...
[pre]
10032 11 Hull Ave 10028 5 Renner St
10028 3 Palmer Dr 10032 2 Hutchinson
[/pre]

I gotta confess something. I'm retired, so I get to think about things a lot more: y-know, cosmology, ontology, epistemology. so I'm relating a bunch of stuff. cogitating and ruminating.

When I started to look at your post, I said to myself, "Self, something here looks awful familiar." And VOLA, y'all I found your earlier Thread, "Arranging records in excel (VLook Up?)"

Well if you read my response and understood the concept, you'd be able to solve this problem, or at lease have a direction to explore.

But for the life of me, I can't understand how you could be so unlucky to have all these mixed up tables. In over 25 years of spreadsheet use I can't remember running across tow malformed tables in less than a week! Can you help me understand how these poor relations came to be?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[zelgar]

Since the data is from 2 different worksheets, you'll need to do the following steps:
1. Identify your 2 different worksheets (e.g., source & destination)
2. Have a loop to go through each of your entries in the source sheet
3. Identify the item(s) you want to match & what you want to copy
4. Loop through your destination sheet to find a match
5. Using IF THEN statements to see if you have a match, put the information in the destination cells
6. Close the loops

Note: with the loop in the destination sheet, you can set it up to break out of the loop if you know you'll only have 1 occurrence, but by default I'd make it loop through all of the entries.

Let us know if you need any assistance on how to set up your macro, but provide us at least the code you have started or a sample worksheet with your macro to make things easier for us.

 

[SkipVought]

@zelgar, "Let us know if you need any assistance ..."

Good luck. Our OP does not have a very encouraging track record. Over the past 5 years or so the OP as responded to only one third of the threads started, and has not as yet responded to a thread started within the past week. Don't hold your breath. ;-)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Chelsea7]

Unfortunately, I'm not as knowledgeable with VBA. I'm learning as I go. This was assigned to me after the other person left the department I'm in. I have no one to assist there.

Here is a sample of the code and table.

MACRO CODE
Sub Macro1p()
'
' Macro1p Macro
'

'

Cells.Find(What:="10032", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
End Sub

TABLE
10032 11 Hull Ave 10028 5 Renner St
10028 3 Palmer Dr 10032 2 Hutchinson

 

[SkipVought]

@Chelsea7, what's going on? You posted another thread back on May 8 with a similar very unorthodox table structure. Never responded to that one, and now there's this very strangely similar wierd structure. Plus the fact that in general, you don't respond to the threads you post. What's that all about.

If you want help you've got to be involved and answer relevant questions regarding your stuff and let us know that a posted solution is or is not successful.

So did my solution in your post of May 8 help you and if so, do you see any part of that that might work in this case?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]