MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Updating Pivot Table Ranges automatically

  Asked By: Asksuresh    Date: Feb 26    Category: MS Office    Views: 1074

I have a problem with pivot tables.
I have for example 2 columns (A and B) in sheet1, one called name the
other called age. Currently I have 20 rows populated with data and I
have created a pivot table and chart to present the data.
I need to be able to add to the 2 columns add I would like the pivot
table to automatically refresh including the latest addition of data
in column A and B. I do not want to reset the pivot tables manully
each time I enter data. I have attempted to do this iin VBA and failed
Is their a VBA solution for this?



2 Answers Found

Answer #1    Answered By: Atid Boonliang     Answered On: Feb 26

The approach I would take would be to create the pivot  table from rows  1 to,
say, 400. then when the pivot table  is refreshed it will take into account
any new rows added. It may have an entry for the blanks but generally that
does not matter.

Answer #2    Answered By: Hayden Evans     Answered On: Feb 26

I can add  one more suggestion. Create a dynamically named range for the
data area. Dynamically named ranges  are created  using offset function. I am
giving a brief example  below

· To Expand the named range Down to as Many Rows as There are Numeric
and Text Entries.

a. Define a name (Insert -> Name -> Define). Let us assume you give the
name 'Test'

b.In the *Refers to* box type: *=OFFSET($A$1,0,0,COUNTA($A$1:$A$400),2)*

c. Your A column  entry is assumed to start at A1 and expand upto A400.
(These values can vary ofcourse)

d. After defining the name Test, create pivot  table.

e. In pivot table  denition, instead of address for the area, give the name

The pivot data  area will become dynamically defined and the refreshing will
take place with newly added data automatically.

Didn't find what you were looking for? Find more on Updating Pivot Table Ranges automatically Or get search suggestion and latest updates.