When you record the steps in creating a pivot table, Excel inserts a
specific range of cells into the code. For example,:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R462C6").CreatePivotTable TableDestination:="", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
You can replace the SourceData string with something more flexible.
You'll always have the same starting cell, so you can use its
CurrentRegion to define the region that you want included in the pivot
table.
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Range("A1").CurrentRegion).CreatePivotTable TableDestination:="", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
Quote:
> I want to create a pivottable by use of a macro and the size of the data
> (the number of rows) to pivot will vary from day to day. I probably need to
> do something like search for the 1st blank cell and then select all of the
> data above the cell to pivottable, but wouldn't have a clue how to do this
> because of my inexperience.
> Any help would be appreciated.
> Many thanks.....
> Shane Clark
Pivottable