Solved: Dynamic Pivot Table

[JKwan]

I was wondering how can I create a dynamic Pivot Table where this table can grow Row wise (and or Column wise as well). Currently, I have to delete and re-create it everytime.

Thanks

[Trevor]

you insert, update queries to add rows

[matthewspatrick]

I usually handle this by:

1) On the data worksheet, set up a dynamic Name, such as PivotRng, referring to:

=Data!$A$1:INDEX(Data!$1:$65536,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

2) Use that Name as the source for my PivotTable

3) Use code like this on the Activate event for the worksheet with the PT:


Private Sub Sheet_Activate()

Me.PivotTables(1).RefreshTable

End Sub

[JKwan]

Thank you Patrick - work like a charm

[matthewspatrick]

Glad to help :)