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:=xlDatabase,
SourceData: alldata).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable10", DefaultVersion:=xlPivotTableVersion10
The program stops at ActiveWorkbook.PivotCache command. There is something
wrong with part- SourceData: = alldata
--
Richard
Bookmarks