You could use a dynamic range as the source:

http://www.contextures.com/xlPivot01.html

Picho wrote:
Hi there,

I have set up a pivot table and I use the following VBA to refresh.

ActiveSheet.PivotTables(PivotTable1).RefreshTable

My pivot table select the entire columns as its range and when data gets too
large, it crashes the computer.

Could you tell me how to specify the range so it would automatically pick
all the data up to the last row?

I have also specify last row with this VBA:
LastSales = SalesWS.Cells.Find(What:="*", SearchDirection:=xlPrevious,
SearchOrder:=xlByRows).Row


Many thanks




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html