• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

marco to refresh a pivot table ...........................very very urg........!

guntikirankumar

New Member
Dear Friend,

Greeting to you,


my requirement is i am using excel 2003 at the begining i have a excel sheet with 200 rows of data based on 200 rows i made a pivot table but now my excel sheet is having 1000 rows when i am refreshing the pivot table it is taking 200 rows but not 1000 how to write a macro which automatically takes the number of rows in the sheet(weather it may be 100 rows or 50 rows or 1000 rows ) and do the refresh accordingly.


advance thanks
 

Hui

Excel Ninja
Staff member
Guntikirankumar


You can use a Named Range for the pivot table source rather than a fixed range

That way you can make it automtically adjust size as you add new rows or columns


Assuming your data starts in A1

Use a formula for the Named Range like:

=OFFSET('1'!$A$1,,,COUNTA('1'!$A:$A),COUNTA('1'!$1:$1))

Where '1' is the sheet name

Adjust ranges to suit


This way you can just refresh the pivot table and if new data has been added it will update for that new data
 

JesseH77

New Member
For that matter when you create the pivot table in the "Table/Range" field just select entire columns, then when you refresh the data any new rows will automatically be included.


The only challenge you'll have is if you have filters they will not automatically select the new members.


Jesse
 
Top