PDA

View Full Version : Solved: Dynamic Pivot Table



JKwan
04-18-2008, 02:27 PM
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
04-18-2008, 03:10 PM
you insert, update queries to add rows

matthewspatrick
04-21-2008, 09:22 AM
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
04-21-2008, 10:19 AM
Thank you Patrick - work like a charm

matthewspatrick
04-21-2008, 10:20 AM
Glad to help :)