Macro to extract a complete data table from one WB to another

**AGrace** · 12-13-2010, 08:24 AM

Hello all,

I'm working on a VB excel doc, and would like to know the best method of copying a complete data table (all data from a worksheet) from one workbook to another?

I would also like to know if it's possible for the macro to extract this data from another workbook without the other workbook needing to be open, but it doesn't really matter if it does.

The workbook that will be providing the data will have a persistent name (production_report), and be located in c:\Production Report\. Ideally, I'd like to open the "production_report" from within the "SummaryGenerator" workbook.

My workbook that will digest this data will be called "SummaryGenerator".

I've attached example workbooks. I need the data table from "production_report.xls sheet1" to be copied to "SummaryGenerator.xlsm sheet3 (Import)".

If anyone can give me a few pointers I'd be very grateful!

Thanks

Adam

NB: I've asked this same question on Mr Excel last week, but have yet to receive a response. http://www.mrexcel.com/forum/showthr...03#post2542103

**johnjohns** · 12-13-2010, 08:43 AM

Do you really need a macro? Keeping both the w.books open, you can copy an entire sheet to either by right clicking the sheet name tab and choosing 'move or copy' option

rgds

johnjohns

**AGrace** · 12-13-2010, 08:53 AM

Originally Posted by johnjohns

Do you really need a macro? Keeping both the w.books open, you can copy an entire sheet to either by right clicking the sheet name tab and choosing 'move or copy' option

rgds

johnjohns

This is a fair point, but I'll be analysing dozens of reports, and having the ability to open then reports within the summary generator and have a macro suck out the info would dramatically speed up an otherwise lengthy manual task (which is what macro's are designed for no?).

so in answer, if it was just the one or two reports I'd definitely do it manually but unfortunately this isn't the case.

**johnjohns** · 12-13-2010, 09:57 AM

Perhaps this way!

First run the macro to copy your files to a sheet and then next one to copy the contents to your master file. See the attachment. Change the directory name

rgds

johnjohns

**AGrace** · 12-13-2010, 11:57 AM

Thanks Johnjohns, I was having a few problems with your macro but while working on it I actually found a solution. It's a little choppy but it's serving a purpose!

**johnjohns** · 12-14-2010, 12:34 AM

Yes it is a little choppy. I made some changes to my existing macro for updating purchase orders and sent to you in a hurry as it was time to leave the office. Anyway glad that it helped you.

rgds

johnjohns