Thread: Pivot table refresh ??

Question: "Is it necessary to make the LIST dynamic to be able to refresh a PT and see additional rows in the source? Or, can I just define the source and let the automatic LIST expansion somehow be reflected in the PT?

We could provide better answers if you upload your workbook (remove/disguise sensitive data).
IMO, if you expect the source data to grow/shrink, then the PT should be based on a dynamic named range. Are you certain you have created a DYNAMIC named range and not simply a named range (static)? At least, post the formula for the named range.

Edit: You can check to see if the Pivot Table is updating by doing the following:
Change a numeric cell in the source data to an extreme number or delete a few cells.
Go to the PT, note the existing results, then do a refresh. If it updates, then the problem with it not updating relative to your named range points to the named ranges as the possible cause of the problem.

Palmetto is absolutely right that uploading an example would help, but to answer the specific question:
It is not necessary to define a dynamic range in order to refresh a pivot table and have new values in the source data reflected in the pivot. It is necessary to define a dynamic named range if you need to redefine the size of the pivot source. In terms of manual refresh or VBA, there is no functional difference - VBA is 'simple', it's not a bad understanding to think of many VBA methods as simply pressing the relevant buttons for you.

I think, however, that your problem is possibly a bit more simple. Defining a DNR is one thing, you then need to set the source of the pivot to that defined name. NB the default name inserted by the pivot table wizard will not be a named range (i.e. if your DNR, "myrange", evaluates to A3:C10 the PT wizard will propose the source A3:C10, which would need to be overwritten with "myrange").

CC

Originally Posted by CC

In terms of manual refresh or VBA, there is no functional difference - VBA is 'simple', it's not a bad understanding to think of many VBA methods as simply pressing the relevant buttons for you.

I think, however, that your problem is possibly a bit more simple. Defining a DNR is one thing, you then need to set the source of the pivot to that defined name. NB the default name inserted by the pivot table wizard will not be a named range (i.e. if your DNR, "myrange", evaluates to A3:C10 the PT wizard will propose the source A3:C10, which would need to be overwritten with "myrange").

CC

Thanks for the ideas. My application is basically a check register so I NEED to have the ability to have successive 'rows' added to the LIST. To that end, I created a DNR with this formula: =OFFSET(Sheet1!$A$4,0,0,COUNT(Sheet1!$A:$A),9) named Actual. (there are 9 columns) Next I went to my pivot table and ran the wizard, backedup, and inserted Actual as a name. I got "REFERENCE NOT VALID".

Also, when I goto a new row, XL takes about 20 seconds to 'reclaculate the PT'.... I don't think that is how it supposed to be. The only VB I have is this:

Code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim pt As PivotTable
Application.EnableEvents = False
For Each pt In Sheets("sheet2").PivotTables: pt.PivotCache.Refresh: Next pt
Application.EnableEvents = True
End Sub

Here is the file: (I just figured out how to upload it) budget2.xls

First, please amend your thread to use code tags per forum rules.

Your Dynamic Named Range is invalid because you are using the wrong function in the formula. You should be using COUNTA not COUNT. The latter only works with numeric values, whereas COUNTA works with both text and numeric values.

I would place the code in Sheet2 and use the sheet_activate event.