Syntax problem defining range for pivot table
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 |
All times are GMT +1. The time now is 03:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com