Long list aggregating sheets in same book

Hi . Good site!

What I need help with is as follows:

I want to create a long list summary sheet aggregating the rows of data from 5 other sheets in the same book.

Formatting, column headings etc are consistent across the sheets, but the number of rows of data on each sheet is changing regularly

In my imagination this is a function which might be simple with some programming knowledge. I am afraid I am an excel user (relatively new to this 2010 version) with no experience of macros & programming to speak of.

Can I readily build this aggregated list function?

Thanks for whatever help you can offer

Thanks Jerry! I find it extraordinary and delightful both that:

1) you have so quickly and readily solved what was to me a tricky problem

2) the code you gave me worked the first time I tried it - no errors, no failures or fiddles or anything... brilliant!

As for the double posting, I do apologise. It was not a tactic so much as an uncertainty and indecision on which was the right place to ask. With hindsight I sense that either would have done, but "both" was the ONLY wrong answer!

oh well - we live and learn - I hope I qualify for first post leniency...

I do have one follow up query:

having used the macro code, I now have a nicely generating aggregation of my sheets.

The "consolidate" sheet, however, will not retain all cell formats column widths for me - whenever refreshed the whole width of the sheet is the minimum to accommodate the longest single row all on one line - which is too long in this instance - and loses dates and so on


I shall go on searching, but in case I am missing an obvious trick here, can you tell me how to retain all formatting & layout on my "consolidate" sheet

Thanks again