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

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

Wow... that was fast, Jim...!
Assumption about "A" is correct - there should always be something in there
I just tried it - this works - excellent - thanks!
Only question I have is that I am constantly extending the columns in the
spreadsheets so that "HP" may only be the last column for today... any
suggestions on how I could accommodate that variability...?

--
Mike Watkins


"Jim Thomlinson" wrote:

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

Just trying to give an idea (not tried the code)

Maybe something like

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=Activesheet.Range(Activesheet.Cells(co lumns.count,"IV").End(xlLeft))

could help?

Martin