• 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


  • 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........!


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


Excel Ninja
Staff member

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:


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


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.