Pivot Table with changing reference.

**Michael.H.** · 06-23-2009, 03:48 PM

Hi. I'm a beginner at Excel and I'm sorry for the long explanation, but it might help you understand my problem.

I'm trying to take data from multiple worksheets and copy them into a summary sheet in the same workbook, and then make a pivot table from the summary sheet. I got the first part done, but I can't figure out how to make the pivot table to incorporate new data in the summary sheet as new worksheets/data is added.

Can anyone help, please?

(I included what I'm trying to do in the attachments.)

**T-J** · 06-23-2009, 07:11 PM

See the answer here http://www.excelforum.com/excel-prog...hin-macro.html for a similar problem to yours.
The method involves creating a dynamic named range (with the Excel menus) that automatically expands/contracts as data is added/deleted from a worksheet.
It was originally written for Excel 2003, but I have since tested it with Excel 2007 - only a few details are different but the principle is the same.