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

[guntikirankumar]

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]

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]

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