updating pivot table to include additional rows

[Guest]

Tried Refresh Table to update the pivot table. My experience with the
Refresh Table will update the values in the data that I've used to create the
pivot table with. My issues is adding additional rows to the data and having
these new rows added to the pivot table. for example, creating a pivot table
asks the range of cells to create the table with, A1-G343. If I add rows
A344-G454, these are not included in the data. I'd like all rows A1-G454
included in the pivot table without redefining the pivot table.

Is it possible to do this?

 

[Debra Dalgleish]

You could use a dynamic range as the pivot source -- there are
instructions here:

http://www.contextures.com/xlPivot01.html

 

[Guest]

Debra - thank you. This looks like what we'd like to do.

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know.

Debra - thank you. This looks like what we'd like to do.

:

 

[Guest]

Hi Debra,

I have used a dynamic range, but the pivot table doesn't auto refresh, is
there any way to make the pivot table update automatically as new data is
added?

 

[Ed K]

Well, this should not be as difficult as the solutions suggest here. Up
until Excel 2007, all you needed to do to keep pivot tables working when rows
are added to an Excel database was to redefine the ranged name by updating
the last row number in the definition screen. Took about 3 seconds to do
this. Been working for me for 10 years with the exact workbook (tracks
invoices by client, by project for the business I own).

Now we spend way too much time trying to figure out why Excel 2007 can't
handle a simple matter of new rows in an Excel database. The real question
is: What are we missing? Or, am I just too naive in thinking that there is
no way MS could have mangled it this much.

I've almost given up trying to solve this problem, either by myself, or with
the help of MS Excel experts that visit this site periodically.

Ed

 

[TopRoper]

' refresh_pivots Macro
' refreshs all the pivot tables in the report
' set the source range of cells to to be used to refresh the pivot table

Dim cellRange As Range

' define data source range
Windows("Data.xls").Activate
Sheets("Datasheet").Select
Set cellRange = Range("C1", Range("C1").SpecialCells(xlCellTypeLastCell))

' refresh pivot based in newly defined data source (including newly added rows
Windows("Pivot.xls").Activate
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=cellRange, _
Version:=xlPivotTableVersion10)

HTH

 

[Ged]

Well, this should not be as difficult as the solutions suggest here. Up
until Excel 2007, all you needed to do to keep pivot tables working when rows
are added to an Excel database was to redefine the ranged name by updating
the last row number in the definition screen. Took about 3 seconds to do
this. Been working for me for 10 years with the exact workbook (tracks
invoices by client, by project for the business I own).

Now we spend way too much time trying to figure out why Excel 2007 can't
handle a simple matter of new rows in an Excel database. The real question
is: What are we missing? Or, am I just too naive in thinking that there is
no way MS could have mangled it this much.

I've almost given up trying to solve this problem, either by myself, or with
the help of MS Excel experts that visit this site periodically.

Ed

 

[Ged]

I've just had the same issue and I thought the info on this page would help
me resolve it, my hopes were dashed when I read your reply. Then the cavalry
arrived. The resolution I've used is as follows:

' Set the source range of cells to be used to refresh the Revenue pivot table
Dim revRange As Range
Windows("Revenue 2008.xls").Activate
Sheets("Revenue").Select
Set revRange = Range("C1", Range("C1").SpecialCells(xlCellTypeLastCell))

' Now refresh the pivot tables
Windows("Pivot 2008.xls").Activate
Sheets("Rev").Select
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=revRange, _
Version:=xlPivotTableVersion10)

Hope it helps