ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Syntax problem defining range for pivot table (https://www.excelbanter.com/excel-programming/356001-syntax-problem-defining-range-pivot-table.html)

Richard

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