Dynamic Pivot table range

[Guest]

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

 

[Guest]

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

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

 

[Guest]

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...?

 

[Guest]

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

Maybe something like

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=Activesheet.Range(Activesheet.Cells(columns.count,"IV").End(xlLeft))

could help?

Martin