Macro to consolidate nonblank cells from multiple worksheets

Hi all,

I have 5 different worksheets in a single workbook, one for each day of the week ('MON', 'TUE', etc). I would like to create a macro that looks between C6 and C500 on each of those sheets, and returns all nonblank values to a sixth worksheet ('SUMMARY') from B3 downwards. I've attached an example sheet.

If possible, I would also like to grab corresponding values from the G and H columns in the daily sheets and drop them into the C and D columns in the summary sheet.

My VBA knowledge is limited to simple modifications of other people's code, but I would be grateful for any level of assistance with this - thanks!

Kevin

I installed a macro called ConsolidateSheets into your workbook. The base code I tweaked from came from my website here:

ManySheets to 1 Sheet (see part 1)

Then I put a worksheet_activate macro into your SUMMARY worksheet so that it calls that macro automatically every time you bring the SUMMARY up onscreen.