Auto-find and copy data from one worksheet to another

[dooray]

Hi all,

First off let me explain that I am an utter novice at Excel - I can just about make the squares pretty colors, do big letters and small letters, etc etc. So forgive the lack of tech speak.

Basically I'm trying to make a macro that will automatically search a worksheet for multiple words/phrases, select the cell next to it, copy it, and paste it to a different worksheet in the cell next to the same word. Confusing? Here's an example:

Worksheet A contains the following:

Chris | 2000
Bill | 3500
Tom | 6000

With the | representing a division between 2 different cells.

Worksheet B contains the following:

Tom |
Bill |
Chris |

Obviously with the second cell blank, and the names in a different order (so it can't just be copy/pasted as is).

What I'd like is to be able to set up a macro that, in this example, will automatically fill out the contents of the 2nd cell from worksheet A into worksheet B, based on what is in the first column. It will be on a significantly larger scale than this example but the same principle applies (I think).

I'd greatly appreciate any help, thanks for reading this gibberish. If anyone needs further clarity I'll do my best to explain.

[DannyJ]

Hi,

Welcome to the forums.

Could you not just use VLOOKUP instead of a macro?

If not then can you please upload a sample sheet for me to take a look at.

Thanks

Danny

Please Login or Register  to view this content.

If you insist on Macro then the code is below.

Please Login or Register  to view this content.

[dooray]

Hi,

Welcome to the forums.

Could you not just use VLOOKUP instead of a macro?

If not then can you please upload a sample sheet for me to take a look at.

Thanks

Danny

Please Login or Register  to view this content.

Hi Danny,

Thanks for your reply. It may well be that I could use VLOOKUP - however I don't know what it is, or how to implement it. Like I said, utter novice.

I will upload an example sheet for you to take a look at. Thanks again.

[dooray]

test.xls

I have attached an extremely crude example. What I am trying to is automate the process of copying the contents of Sheet 1, column H, to sheet 2, column E, but keeping the sales next to the correct entry in the first column.

[DannyJ]

Hi Dooray,

I will put the macro into that worksheet, is that worksheet exactly how the real spreadsheet is set up?

Regards

Danny

[DannyJ]

Hi Dooray,

Try this code, if you worksheet is set up exactly the same as that then this will input the VLOOKUP code in for you.

Please Login or Register  to view this content.

EDIT: Also if you want to move cells around etc. then you will need to take out the .Value = .Value as it only puts the result of the formula in the cell. If you take that out then it will put the actual formula in the cell.

Regards

Danny

[dooray]

Not exactly but it's very similar - the only differences would be minor formatting etc. Many thanks again!

Thanks for the code... would you mind explaining how to insert it into my spreadsheet? This is literally the first time I've ever attempted this kind of thing.

[DannyJ]

No problems. I am still learning myself! Who isn't?

When in the workbook you want to put it in press Alt + F11, this will open the Visual Basic Editor. It will either open with a plain white page or a gray page. On the left is a list of your workbooks that you have open, double click on the sheet you would like to put the formula in. Then copy and paste it and save.
Once that is done close Visual Basic and press Alt + F8 which will bring up a list of macros. Select the one called Insert_VLOOKUP (It should be the only one I would of thought) and click run. This should run the formula for you.

A few things to be careful of, my code refers to sheet names so you have to make sure that your sheets are named what I refer to or that will need to be changed. It also has cell references so the cells have to be correct for it to work.

Make sure to come back if you have any problems!

Regards

Danny

[dooray]

Just tested it, and it works perfectly. I will have a play around with setting it up on the full sheet.

Thanks so much for your expertise and patience, I didn't expect such a speedy reply!

[DannyJ]

No worries dooray, anytime. Please mark the thread as solved. To do this click on thread tools at the top of the page and 'Marked thread as solved'

[dooray]

I've been playing around with the code you provided, turns out I have no idea how to amend it for my own needs. Would you mind having a look at a more detailed example sheet?

In this example, I would need it to work the same way as before - copy the data from column H on the 'Sales' worksheet to column B on the 'Area' worksheet, based on the names in column A on both worksheets.

If the formula doesn't find a match, it would just leave the sales cell empty, or insert a zero. Obviously some of the names entered in column A on 'Area' won't match column A on 'Sales' - I have just amended 4 of them so that they match, as a test.

<snip>

Hope you have time to take a look again for me. Thanks Danny!

[DannyJ]

To simplify things slightly. Copy and paste the below formula into cell B5 and then auto fill the rest of the cells.

Please Login or Register  to view this content.

As you are using Excel 2010 you could probably use the below formula instead,

Please Login or Register  to view this content.

I have never used anything but 2003 so not sure if the bottom one works but the top one definitely does.

Regards

Danny

[dooray]

Okay, would you mind clarifying what amendments would need to be made to the VLOOKUP script you provided earlier to make the second example spreadsheet I uploaded work?

I naively thought I'd be able to alter your original code, but I have no idea what I'm doing.

[DannyJ]

Dooray,

I didn't amend the code. If you would like me to then I will. I did it purely by typing it into cell B5 (You can copy and paste from above) and then autofilling it down from there.

Regards

Danny

[dooray]

I hate to say it.. but I'm not entirely sure what you mean. I don't even know about autofilling, shamefully (as it sounds easy).

If you wouldn't mind amending the code for the 2nd test spreadsheet I provided I'd really appreciate it.

[DannyJ]

Dooray,

No worries. It is below for you.

Please Login or Register  to view this content.

All I have amended is the .Formala line to the formula that I gave you. This code marks any that don't match as "" which means blank. If you would prefer them to say 0 that is fine.

Regards

Danny

[dooray]

Sorry for the late reply, I sadly had non-Excel matters to attend to.

I have been playing around with your most recent formula and it works perfectly. Thanks again so much for your help, I'll change the thread title to solved now.

[dooray]

Hi Danny (or any other experts),

Discovered one issue with the spreadsheet I'm having to use - some of the entries are split over two lines, which means they cover 2 different rows. Example attached:ex.xlsm

In the example, WANSBECK LONG SLEEVE TEE covers 2 rows, so when the formula searches for it and inserts the sales, it thinks both are the same, when the different colour for each is on the line below.

If you have any ideas of how to resolve this I'd really appreciate it! Thanks!