I need to create multiple pivot tables from a master spreadsheet containing
income data broken out by business area and customer. I want a separate pivot
table for each business area that summarizes the customer data.

I have a program that uses an AutoFilter routine to extract subsets of data
by business area. These are copy/pasted into new sheets with variable row
numbers (starting at cell A1) for each business area.

For each of these variable-length sheets I need to create a pivot table
using the entire subset of data.

My problem: proper syntax for defining the SourceData in the PivotCache

€˜Define range variable €˜alldata as all the cells containing data
Dim alldata As Range

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Set alldata = ActiveCell

€˜Set up cache for pivot table
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData: alldata).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable10", DefaultVersion:=xlPivotTableVersion10

The program stops at ActiveWorkbook.PivotCache command. There is something
wrong with part- SourceData: = alldata



--
Richard