Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Ellen
 
Posts: n/a
Default updating pivot table to include additional rows

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?
  #2   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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

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

Ellen wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

  #3   Report Post  
Ellen
 
Posts: n/a
Default

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

"Debra Dalgleish" wrote:

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

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

Ellen wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


  #4   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

You're welcome! Thanks for letting me know.

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

"Debra Dalgleish" wrote:


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

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

Ellen wrote:

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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 26
Default updating pivot table to include additional rows

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?

"Debra Dalgleish" wrote:

You're welcome! Thanks for letting me know.

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

"Debra Dalgleish" wrote:


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

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

Ellen wrote:

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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 12
Default updating pivot table to include additional rows

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

"Debra Dalgleish" wrote:

You're welcome! Thanks for letting me know.

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

"Debra Dalgleish" wrote:


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

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

Ellen wrote:

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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default updating pivot table to include additional rows

' 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").ChangePivot Cache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=cellRange, _
Version:=xlPivotTableVersion10)

HTH

"Ed K" wrote:

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

"Debra Dalgleish" wrote:

You're welcome! Thanks for letting me know.

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

"Debra Dalgleish" wrote:


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

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

Ellen wrote:

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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


  #8   Report Post  
Posted to microsoft.public.excel.misc
Ged Ged is offline
external usenet poster
 
Posts: 7
Default updating pivot table to include additional rows



"Ed K" wrote:

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

"Debra Dalgleish" wrote:

You're welcome! Thanks for letting me know.

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

"Debra Dalgleish" wrote:


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

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

Ellen wrote:

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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


  #9   Report Post  
Posted to microsoft.public.excel.misc
Ged Ged is offline
external usenet poster
 
Posts: 7
Default updating pivot table to include additional rows

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").ChangePivot Cache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=revRange, _
Version:=xlPivotTableVersion10)

Hope it helps
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Table - max rows allowed in data range dmotika Excel Discussion (Misc queries) 2 May 26th 05 05:52 PM
Pivot Table for survey data w/ questions as Rows & poss answrs as pfwebadmin Excel Discussion (Misc queries) 0 May 17th 05 02:31 PM
Pivot Table Problems Rachel Gonsior Excel Discussion (Misc queries) 3 March 21st 05 08:24 PM
Have a Pivot Table Include a "% of an item" [email protected] Excel Worksheet Functions 1 February 23rd 05 11:32 PM
How do I manipulate pivot table report to include percentage of? KRUEMJ-Needs some help Excel Discussion (Misc queries) 2 January 31st 05 02:06 AM


All times are GMT +1. The time now is 05:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"