My assumption is that you will always have something in each Cell in A...

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=Activesheet.Range(Activesheet.Range("H P4"),
activesheet.Cells(rows.count, "A").End(xlUp))

--
HTH...

Jim Thomlinson


"watkincm" wrote:

I am trying to make a pivot table range dynamic. I have written the macros
to create the table using a static address - now I need the macro to
determine the range size of the underlying database...

Currently I use:
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=Range("A4:HP3014"))

The start point will always be cell A4 - but the last col and row needs to
be dynamic.
So far, the posts on this forum have not fixed it for me (or I have not
understood them correctly) - any suggestions...?
--
Mike Watkins