asked on

Excel 2007 Macro to consolidate data into single sheet

Hi -- I asked this question a few days ago and only got a partial response. I think it's because I made the question sound more complicated than it is.

I am trying to pull the data from all the sheets in a workbook into a single summary sheet. Block pasted one under the other.

1 caveat:

I would like to have the name of the spreadsheet from which the record came in front of each record in the resulting workbook (i.e. in column A) So if record "Apple | 7 | tasty | red" came from sheet "Bob Smith", the resulting record should be: Bob Smith | Apple | 7 | tasty | red

I would be very grateful for any help with this. Kind of a rush!

Thank you!

Andrey

Curt Lindstrom

Check this article by brettdj, it seems to describe your solution

https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_2804-Code-to-compile-all-worksheets-from-either-a-all-Excel-workbooks-in-a-folder-or-b-a-specific-Excel-workbook.html

Cheers,
Curt

ASKER CERTIFIED SOLUTION

StephenJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

StephenJR

By the way, have assumed data will go in "Summary" sheet, so change to suit.

andreyman3d2k

ASKER

Curt, haha this is the exact same response I received the last time I posted this. 2 issues --

1. This article seems to be a bit above my level.

2. It does not seem to place the worksheet name of each record into column A (although it might, I just don't understand the code -- it was not mentioned in the explanation).

Thanks,

Andrey

andreyman3d2k

ASKER

StephenJR, this is awesome. Thank you.

Andrey

Curt Lindstrom

Hi Andrey,

I attached a file with a modified version of Dave's (brettdj) file. Press the button to try. Answer false to the first prompt and true to the second prompt. Select the workbook which you want to consolidate. It will put the result in a new workbook. i got it to do add the name for all sheets after the first sheet. The name is on the green coloured line.

Cheers,
Curt
Consolidate-sheets.xls

Curt Lindstrom

Oops it looks like I'm a bit late with this submission. It helps to refresh sometimes.

Cheers,
Curt

andreyman3d2k

ASKER

Thanks very much, curt, I really appreciate it. Indeed StephenJR's code did the trick, but I am very grateful.

Andrey