Roger Govier wrote:
Hi Dale

You would be very unwise to make your source range the whole columns. It
will slow everything down, especially in XL2007 with 1 million rows.

From a posting I made a couple of days ago


It all depends upon your source data range being Dynamic.
If your source is currently set as $A$1:$M$200 and you then add data to
rows
201, 202 etc, then Refresh will have no effect, as you have told Excel only
to use data down to row 200.

Different options exist dependent upon the version of Excel you are using.
For XL2003
Place your cursor in the first row of your dataDataListCreatecheck my
List has Headers
Then Place your cursor in the first cell of this List and dataPivot
TableFinish and it will use the List as the source.
The List is Dynamic and will grow as you add more data.

In XL2007
Place cursor in first row of dataInsert tabTablecheck my table has
HeadersGive it a different name if required in the Table Name box of the
Design tab that appears
Place cursor in data tableTable DesignSummarise with Pivot Table.

For any version of Excel, you can create your own Dynamic named ranges.
For more information on this take a look at a tutorial I wrote at
http://www.contextures.com/xlDataVal15.html

If you want your Pivot Table to update automatically, without having to
click the Refresh button, then, assuming your PT is on a different sheet to
the source data, copy this simple code to the PT Sheet.

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables(1).PivotCache.Refresh
End Sub


To USE
Copy code as above
Right click on sheet tab with PTView Code
Paste Code into white pane that appears
Alt+F11 to return to Excel

I hope that this helps clarify the issue


All good points, but I would like to share my experience.

Using E2003, I see no performance hit whatsoever when referencing full
columns. I have hundreds of pivot tables that do this, and often have 5
or more caches in a workbook. I suppose when E2007 rolls around to my
company I will get to see how well (or poorly) this technique scales.

The only downsides of full columns in E2003, both minuscule, are (blank)
becomes a PT category and numeric data items do not default to Sum. The
advantage is I can easily refresh a cache with a single click, and not
resort to updating the (potentially large) cache every time I activate
the PT, as the VBA solution would do. Perhaps a better alternative would
be to call that code on demand with a toolbar button or some such.

Lists do not seem to work well in E2003. I have found that a worksheet
with a list will cause Excel to use 100% CPU while the worksheet is
active. This may not happen the first time the List is created, nor the
second, but eventually it seems to happen. Because of this, I gave up
using Lists as dynamic ranges. I suspect this is a bug and possibly a
rare one (I have not researched it); I hope it has been corrected in E2007.

A formula-driven dynamic range seems like a reasonable choice if you do
not mind setting it up and embedding volatile formulas in the workbook.

Regards, Smartin.