Macro to copy data from multiple sheets to one (new) master sheet

**nhtodd** · 02-02-2011, 12:18 AM

Hi;

I have a large amount of sheets (500+) in a workbook. Each sheet is named with a serial number but not all sheets are sequentially numbered. The data locations within each sheet are identical, but not in one simple row. I need to copy about 15-20 data points from each sheet into the next blank row of a new master sheet (I'll call it MASTER). I want to end up with one sheet with 500 rows rather than one workbook with 500 sheets. Then I'll be able to better analyze the data. So for example: I want to copy H3 from (Sheet)1234 to A1 of MASTER, B3 from Sheet1234 to B1 of MASTER, C4 from Sheet1234 to C1 of MASTER...etc. and continue for about 17 other datapoints from the first sheet)then...H3 from (Sheet)1245 to A2 of MASTER, B3 from (Sheet)1245 to B2, C4 from Sheet1245 to C2, etc... I'm not sure if one macro can do this for all sheets or if I would need to run the macro from within each of my sheets.....any help or advice you can provide would be greatly appreciated!!

**snb** · 02-02-2011, 04:26 AM

Please Login or Register  to view this content.

**JBeaucaire** · 02-02-2011, 05:04 AM

My hat's off to SNB for another marvelous macro.

But taking to heart his signature, you might find this less elegant approach at least easier to understand.

In the workbook you want to merge, add a new sheet called SUMMARY, then run this:

Please Login or Register  to view this content.

Having said that, if SNB's works for you and you can edit it as needed, it is far more efficient.

**nhtodd** · 02-02-2011, 07:59 PM

Hello JB and snb;

A HUGE thanks to both of you for the quick replies with solutions. Although I didn't understand SNB's code suggestion I do appreciate its simplicity and plan to try it out. JB - I tried your suggestion and it worked perfectly!

Thanks again,
Todd

**andre.wasserman** · 08-24-2012, 02:30 AM

I'm looking at copying data blocks from 300 sheets into sheet 1, one below the other.
Will test both methods & post result. Thank you for the valuable information.