Pivottables in a macro

[Michael]

Hi, I need to create a pivottable in a macro on a set of
data that is not fixed (ie could be a different size each
time the macro is run)I presume that the sourcedata needs
to be amended and presume it should utilise a
currentregion formula but cannot seem to get it to work.


ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
"Sheet1!R8C3:R10C9").CreatePivotTable
TableDestination:="", TableName:= _
"PivotTable12",
DefaultVersion:=xlPivotTableVersion10



Any ideas?

 

[Debra Dalgleish]

Use a dynamic formula to name the source range:

http://www.contextures.com/xlPivot01.html

and refer to that name in the code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="Database").CreatePivotTable _
TableDestination:="", TableName:="PivotTable1"