Copy rows from several sheets and compile into list on another sheet

**Andrew-Mark** · 01-14-2010, 12:57 PM

I am not good enough to do this on my own and you folks have always helped me out in the past.

I attach a demo workbook to try and show my problem.
I have a workbook with 9 sheets of data on them with a final 10th sheet where I wish to combine all this lovely info.

The data on each separate sheet is for different suppliers and is likely to grow so I cant just copy and paste the info across.

Is there a way for Excel to automatically on opening/closing (pref closing as that will be when I have amended the data) to search for rows where there are entries and then copy those rows only onto the 'Overview' sheet. I would prefer the data to have no blank rows and to have the first sheets data at the top of the list on so on down.

Can it be done?

I would also appreciate, if it can be done, if you would explain your solution so I might learn how you did it.

Many thanks,

Andrew

**JBeaucaire** · 01-14-2010, 01:19 PM

1) Right-click on the OVERVIEW sheet and select VIEW CODE
2) Paste in this sheet event macro:

Please Login or Register  to view this content.

3) In VBAProject window on the left in the VB Editor, doubleclick on the ThisWorkbook module to open it.
4) Paste in this workbook event macro:

Please Login or Register  to view this content.

5) Close the editor and save your sheet as macro-enabled workbook. (*.xlsm)

=========
Now, every time you VIEW the OVERVIEW sheet it will update to the most current values from all the other sheets.

Anytime you try to save the workbook, it will activate the OVERVIEW sheet first to force it's bultin macro to run first.

**Andrew-Mark** · 01-14-2010, 01:35 PM

Thank-you very much that is doing exactly what it said it would.

Trouble is, and I know I never said, I have cells that have values derived from other info on the original sheets. How do I get it to copy values only?

**JBeaucaire** · 01-14-2010, 05:14 PM

Change the Sheet macro to:

Please Login or Register  to view this content.

=========
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)