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

[havocdragon]

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:=xlDatabase, 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]

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

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:=xlDatabase, 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