I'm trying to create a macro that will compare data in two seperate files (spreadsheet A and B) and if it matches, return the data.



Here's the details. The macro will look at columns B, C, D, and E in spreadsheet A. If column B matches column C in spreadsheet B, then add columns C, D and E from spreadsheet A into columns J, K and L in spreadsheet B.

Any help would be greatly appreciated it.

You can do this with a macro. But first let me ask: Why not do it with worksheet functions instead? A macro may be better, depending on your needs, but functions are often simpler, and also they keep the data updated rather than having to renew the match-and-add from time to time. So before we do a VBA program, are you sure you want to do it that way? If so, why?

(Hint: If the answer is "Yes, because I want to learn VBA", that's an acceptable reason.)

Indeed it can be done easily enough using a combination of Excel's IF, INDEX & MATCH functions, without the need for a macro. Using the functions provides somewhat greater flexibility than a macro as they can update the output automatically according to changes in the inputs.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Pardon me for jumping in. I noticed something in the OP that made me question the replies.

I can understand using the functions on two worksheets with in the same workbook. That makes sense.

If as I'm reading it - two workbooks - you would need to use VBA wouldn't you? If that is not the case would you mind pointing me toward how to do this? I would like to learn this as well.

You don't need VBA - it can be done with formulae that same as you might when comparing data on two different worksheets in the same workbook. Depending on the formulae used, you might need to have both workbooks open to do any updating.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

He asked "how", Paul. Scott, try this:

1) Open two workbooks A and B.

2) Select an empty cell in A.

3) Type this sequence: "=<Ctrl-Tab><Down><Enter>".

Observe the formula that Excel just created; it shows the syntax you must use when pointing a formula to another open workbook.

(If you're not sure what just happened: "=" started the formula. <Ctrl-Tab> moved from workbook A to workbook B. <Down> moved the cursor down one row, pointing to the next cell (probably A2, but whatever). <Enter> completed the creation of the formula.)

Next:

4) Close workbook B.

Now look at the formula again; the syntax has changed a bit, showing what you must use to point to another closed workbook.

I usually find it easier to let Excel set the syntax, and then repeat it myself (modifying it and copying it down the column or whatever is appropriate), rather than typing it out myself and possibly forgetting a critical piece of the formula. And by the way, I've noticed that it's a little slower doing lookups to another workbook, and a lot slower if that workbook is closed. YMMV.

Well I'll be dipped!

BobBridges - You were correct I was looking 'how' to do it. I tried your instructions an see exactly what you are talking about. Several warnings about security and connections to external data but works like a charm.

I'm really enjoying this forum. I've learned tons just reading!

Thanks for the clarification.

ksigcajun - sorry for jumping in the middle of your post!!

Ah, it's fun being appreciated . Remember what I said: In my experience, I've found that many VLOOKUP-or-whatevers to an external closed workbook is significantly slower than if it's open. I have one application where I write the VBA code to actually copy the table into a worksheet in the current workbook, every time it runs, to be sure of getting the latest info but without the drag on the lookups itself. Mind you, that one has a short lookup table but tens of thousands of rows doing lookups on it. If it were only a few hundred lookups it might not matter.