Copy/Paste Range of Data from Multiple Workbooks/Worksheets to Master Workbook/Worksheets

Greetings,
I've been searching and searching for a solution. I've been able some threads that address parts of what I need, but I'm such a VBA novice, I don't know how to put them all together. I'm hoping someone can help me create the code to get this done ...

Excel 2003
Windows v8

--WHAT I HAVE--

I have three workbooks: Workbook_A, Workbook_B and Workbook_C. Within these workbooks, each has two worksheets: Sheet_1 and Sheet_2.

I also have a master workbook: Workbook_Master. It also has two worksheets identically named as the others: Sheet_1 and Sheet_2.

All four workbooks are saved within the same folder on my computer: G:\Pipeline

**I've attached very stripped down versions of the four workbooks.


Workbook_Master.xlsWorkbook_C.xlsWorkbook_B.xlsWorkbook_A.xls


--WHAT I NEED--

I need a macro button (or buttons), that when selected will:

1) Copy the data from Workbook_A/Sheet_1, Workbook_B/Sheet_1 and Workbook_C/Sheet_1 and paste onto Workbook_Master/Sheet_1 ... with the following conditions:

Range for Sheet_1 on Workbook_A, B & C is A2:Z1000

I need the macro to "look" at the whole range on each sheet but only copy/paste the rows where the data in A2:A1000 = open, settled or closed

Pasting onto Sheet_1 on Workbook Master can begin at A2 (as to not paste over my header row)

Data from Sheet_1 of Workbook_A, B & C needs to be pasted to Workbook_Master/Sheet_1 on the next available row, so they aren't pasting on top of each other

Each time macro is run, pasting can begin again at A2 again and overwrite the data in Workbook_Master/Sheet_1 (or first clear contents, then paste).

**I don't know if it's way too complicated to have one macro that pulls the data from all three workbooks at once. I'm totally fine with three separate macros, just as long as they paste into the next available row, so not pasting on top of each other.


2) Copy the data from Workbook_A/Sheet_2, Workbook_B/Sheet_2 and Workbook_C/Sheet_2 and paste onto Workbook_Master/Sheet_2 with the following conditions:

Exact same conditions as above


**If possible I'd like to be able to run the macro on on Workbook_Master without having to open the other three workbooks.

I think that's all the details. If I forgot anything or need any additional information please let me know. Thank you in advance for your help!!

Are you still working on this?

I am sure I can help you but I don't want to try to create it from scratch so if you could provide the workbooks with generic data that can illustrate the results I would be happy to put code to it.

Cheers!
K

Oh yes, I'm desperate for a solution ... thank you for the reply!!! Attached now to my original post are three sample workbooks (A, B & C) and a master (Workbook_Master). I've gone ahead and put data into the Master workbook to show you what i'd like as a final result.

One thing I don't think I put in my original post - I'd like the data to "paste special" into the master workbook with values/number formats only.

Thank you so much in advance!!

This grew into more than I expected but I didn't give up. There are a great many posts out there on this subject and there really hasn't been a good reliable method presented that doesn't take some major performance hit to do it. But I think I may have a rather unique idea from one of my recent posts to help someone else. I do have a couple of questions at this point though...

May I assume that Workbooks A, B & C will not be open by anyone when Workbook_Master is trying to update?
Also, I may have to change the formatting a bit (for the better) in Workbooks A, B & C; would that be okay? Keep in mind, this one may require me to explain what I did to change them and it shouldn't be hard to update yours but it would have to be updated or the data may need to be pasted as values into a new workbook.

Anyway, paying the bills and playing with this too; I didn't leave you stranded yet!

Regards,
K

Sorry, I have a great procedure that just doesn't work on Excel 2003 for copying the sheet from a closed workbook. With 2003 I may need to open the workbook programmatically. Will that work for you? You won't see it open and it will be closed as soon as it has copied the data. Also I did avoid making changes to your originals.

Please let me know if you still need this, as it has been some time now since you've posted.

Thanks,
K

Hi K,
Sorry, I've been out of town. All that sounds great! One more thing ... each of the source workbooks is password protected. Thanks for your help!!

I don't suppose you can upgrade to 2010 or maybe 2007..?

Regarding, "All that sounds great"; are the source workbooks,by chance, not available to anyone but you? This will take some the complexities out if you can be assured that they will be closed when you update the "Master".

Unfortunately, updating to 2007 or 2010 isn't an option right now.

The source workbooks are available to others, not just me. I can't always guarantee that the workbooks will be closed (not in use by others) at the time of update. Perhaps a simple error message that one of the workbooks is open and must be closed to run the update will suffice?