Your description is rather vague, leaving us to make some guesses.

Sometimes the approach you start with makes it very hard to accomplish your
ultimate objective. Perhaps you'd be better off putting all the events in a
single worksheet, but adding a column that would allow you to designate the
event for each row of data. Then you could easily 1) summarize data that is
in a single sheet, and 2) filter the data by event to be able to see all the
rows for a single event.

If that REALLY doesn't work, try posting back with a better description
(examples?) of your data layout. You'll get much better assistance here if
you educate us.

"DonnaO" wrote:

I have a workbook which consists of a summary sheet, a master sheet, and a
number of sheets which have been copied from the master. The individual
'event' sheets consist of headings and variable data in the next cells which
cover a few columns and rows. The following rows consist of lists which do
not need to be transferred. The summary sheet has the same headings as the
other sheets, but they go across columns. The data from the event sheets
goes below the headings, one row per event. How can I automate this process?
I have tried several functions without success. My latest hope was for
Offset, where I discovered I could only offset by rows and columns, not
sheets. I'd be grateful for any helpful advice