how do I hard code a variable range for a pivot table in vba?
 

Hey all. I have noticed that Pivot Tables when programmed in visual basic
seem to be more picky than some other code. And honestly I dont write much of
the code myself but know where the key fields I need to change are. Here is
my current code

Range("A1").select
Selection.CurrentRegion.Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Report'!R1C1:R797C12").CreatePivotTable TableDestination:="", _
TableName:="Report"

now if this is hard coded it is going to select A$1$:L$797$ everytime. I
want that to be the current region or selection, or utilize the
selection.currentregion.select in that code itself so this will work no
matter how many columns or rows of info there are, it will only create a
pivot table based on those regions.

how do I hard code a variable range for a pivot table in vba?
 

You could use a dynamic range as the pivot source, and refer to the
range name in the code. There are instructions he

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


havocdragon wrote:
Hey all. I have noticed that Pivot Tables when programmed in visual basic
seem to be more picky than some other code. And honestly I dont write much of
the code myself but know where the key fields I need to change are. Here is
my current code

Range("A1").select
Selection.CurrentRegion.Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Report'!R1C1:R797C12").CreatePivotTable TableDestination:="", _
TableName:="Report"

now if this is hard coded it is going to select A$1$:L$797$ everytime. I
want that to be the current region or selection, or utilize the
selection.currentregion.select in that code itself so this will work no
matter how many columns or rows of info there are, it will only create a
pivot table based on those regions.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html