Extract and summarize from 100 worksheets into one

Hi All,

Please see attached Excel sample file. I would like to extract information with the event “Date” happens in June(6/1/08-6/30/08) in Column B on each worksheet. And I would like these data to be summarized from all different worksheets into one. Is there any code I can use so that I can drag these information out into a single worksheet at one time instead of keep looking in each worksheet (about 100) doing copy and paste?

Please be advised.

I appreciate for all your time and help!

Amy

Hi Amy,

Using the code from this posting by Dave Hawley as well as some of my own code (including some smarts), the following macro should do the trick:

Please Login or Register  to view this content.

HTH

Robert

Dear Robert,

That seems to be a miracle to me! I tested the code on the original file i attached and it works beautifully, and of course, smartly!

My last question is, what if there are other columns in between Column D & E, which I noticed your put:
.Columns("D:E").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"

In cases where I have other columns containing Currency, test and date and even some null values where are pending for data entry...etc., do I need to list each different column format separetely based on yours? If so, would you please give me a hint how could I accomplish such code?

I attached a new file which I added some new columns.

Thanks for all your time a help again!

Amy

Hi Amy,

Thanks for the feedback and I'm glad it worked (almost).

I've amended the following to meet the new layout - if you need to do this in the future, the area to adjust is within the

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

block. You can use the recorder the help with the code.

HTH

Robert

Please Login or Register  to view this content.

Hi Robert,

This is awesome! I tested it and this is exactly I want! I can't express how much I should appreciate for your code coz it's gonna save tons of my OT work for the following week.

Thanks again and have a wonderful weekend!

Amy

Dear Amy,

Thanks for the feedback and you're very welcome - I'll send you the bill

I hope you have a good weekend too.

Kind regards,

Robert

Sure, any time.