Combining worksheets - please help

Posted by William Kelly on April 13, 2001 11:41 AM

I have ten worksheets, all with the same headings and type of information below the heading. How can I add an eleventh worksheet and have this sheet contain all the information in the previous ten worksheets? So, I will be able to update the ten worksheets and that would automatically update the eleventh. Do I need a macro? Please help.

Thanks

Posted by Malc on April 13, 2001 4:37 PM

You could use a macro it depends what you want to do. but the non macro way would be to use a pivot table attached to all 10 worksheets. Select the multi source option in step two (I think) Select a data range in the 10 sheets greater than the current data area so when you add anything new all you have to do is refresh the table. Eventually you'll have to reselect the data area

If the data your adding to the ten worksheets is numbers you could use sumif if providing you can identify a unique row lablesbefore adding data.

The macro way is to select each work sheet one at a time.
Select and copy ata
Select the 11th worksheet
Paste
select cell A1
xldown
Select active cell +1
and repeat the cycle for each of the 10 work sheets

Posted by Dave Hawley on April 14, 2001 1:25 AM

Hi William

Have you looked at "Consolidation" under Data on the main Menu ?

Dave

OzGrid Business Applications

Posted by William on April 14, 2001 12:11 PM

Yeah, I looked at it. I wasn't able to completely grasp what it was trying to make me do though. I thought that was going to be the way, I just don't know how to use it.

Posted by Dave Hawley on April 14, 2001 10:01 PM

Hi william

Here are some links that should help:

http://www.lgtraining.co.uk/speed/speed_consolidation.htm

http://www.lgtraining.co.uk/essentials/es_data_consol.htm

Dave

OzGrid Business Applications