You could add some simple event code to the sheet with your data table
something like
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Target.Count > 1 Then GoTo endsub
If Target.Column <> 4 Then GoTo endsub
Sheets("Sheet4").PivotTables("PivotTable1").PivotCache.Refresh
endsub:
Application.EnableEvents = True
End Sub
Insert this in the code module of the sheet with your data.
Alter Target.Column <> 4 to the column number where the last entry of
the new row with your bank transaction occurs.
Change "Sheet4" to the sheet name holding your Pivot Table, and
"PivotTable1" to the name of your PT as shown in Table Properties for
the PT.
--
Regards
Roger Govier
"Dave F" <Da...@discussions.microsoft.com> wrote in message
news:8D140EF5-63ED-400D...@microsoft.com...
>I have a dynamic named range that is a data table of bank transactions.
>How
> can I have my pivot tables automatically update when I add data to the
> data
> table? I would prefer not to have to click the refresh data icon on
> the
> pivot table toolbar if possible.
>
> I'm not sure if it's relevant but here's the formula used for the
> dynamic
> named range: =OFFSET('Acct XXX'!$A$1,0,0,COUNTA('Acct XXX'!$A:$A),9)
>
> Thanks,
>
> Dave
>
> --
> Brevity is the soul of wit.
This is the code I have, modified with the sheet name, column number, and
pivot table name. It doesn't seem to automatically refresh the pivot table,
though. Am I missing something here?
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Target.Count > 1 Then GoTo endsub
If Target.Column <> 9 Then GoTo endsub
Sheets("Acct").PivotTables("PivotTable2").PivotCache.Refresh
endsub:
Application.EnableEvents = True
End Sub
--
Brevity is the soul of wit.
Roger's code refreshes the pivot table when you select a cell in column
9. Do you enter values in that column, or does it contain formulas, and
you skip past it?
Dave F wrote:
> Thanks.
>
> This is the code I have, modified with the sheet name, column number, and
> pivot table name. It doesn't seem to automatically refresh the pivot table,
> though. Am I missing something here?
>
> Option Explicit
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Application.EnableEvents = False
> If Target.Count > 1 Then GoTo endsub
> If Target.Column <> 9 Then GoTo endsub
> Sheets("Acct").PivotTables("PivotTable2").PivotCache.Refresh
> endsub:
> Application.EnableEvents = True
> End Sub
--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html
I entered the code in a module. (Is a sheet module different than a plain
old module? I know class modules are a different beast entirely, and the
code is NOT in one of those.)
To answer your other question, here's how my data is laid out. Column A is
column 1, column I is column 9, and, reading from column A to column I i have
the following column headers: Trans#, Year, Month, Date, Category,
Description, Type, Amount, Balance.
So, column 9 is Balance. That is a formula. The only columns that are NOT
formulas are: Amount, Description, and Date. I suppose then that means that
Amount is the last piece of data I enter in a given row. Does that imply
that I should change
If Target.Column <> 9 Then GoTo endsub
to
If Target.Column <> 8 Then GoTo endsub
Thanks for any insight here.
Dave
--
Brevity is the soul of wit.
Thanks to both of you. I'm not really sure what this code does, but it
works, so now the task for me is to figure it out.
Thanks again.
Dave
--
Brevity is the soul of wit.
If column 8 is the last column in which you enter data, you could change
the code to reference that column.
Or, add code to the pivot table sheet module, to refresh the pivot table
when you activate that sheet. For example:
Private Sub Worksheet_Activate()
Me.PivotTables(1).PivotCache.Refresh
End Sub
Dave F wrote:
> Hi Debra,
>
> I entered the code in a module. (Is a sheet module different than a plain
> old module? I know class modules are a different beast entirely, and the
> code is NOT in one of those.)
>
> To answer your other question, here's how my data is laid out. Column A is
> column 1, column I is column 9, and, reading from column A to column I i have
> the following column headers: Trans#, Year, Month, Date, Category,
> Description, Type, Amount, Balance.
>
> So, column 9 is Balance. That is a formula. The only columns that are NOT
> formulas are: Amount, Description, and Date. I suppose then that means that
> Amount is the last piece of data I enter in a given row. Does that imply
> that I should change
>
> If Target.Column <> 9 Then GoTo endsub
>
> to
>
> If Target.Column <> 8 Then GoTo endsub
>
> Thanks for any insight here.
>
> Dave
--
Dave F wrote:
> Well, I just figured it out. Turns out, Debra, you were right to ask me what
> the last column I entered a value in is, as opposed to the last column that
> calculates a value. Changing the 9 to an 8 fixed the problem.
>
> Thanks to both of you. I'm not really sure what this code does, but it
> works, so now the task for me is to figure it out.
>
> Thanks again.
>
> Dave
--
That's a much better idea as it would get triggered far less frequently,
and only when required..
--
Regards
Roger Govier
"Debra Dalgleish" <d...@contexturesXSPAM.com> wrote in message
news:45650164...@contexturesXSPAM.com...
My apologies for not being more specific in my post, regarding which
column to check - it has to be one in which you enter data, as opposed
to be a calculated column, for the Change event to trigger.
Luckily Debra was around after I had logged off last night, to sort
things out for you.
I hope you caught Debra's later post with here suggestion about using a
Sheet activate event on the PT report sheet instead. That is a far
better suggestion as it only gets triggered when you go to the PT sheet
to look at the result, and will not be triggered every time you enter a
new amount, (or change an amount), so it will be quicker.
--
Regards
Roger Govier
"Dave F" <Da...@discussions.microsoft.com> wrote in message
news:4401444E-D3E9-4AC1...@microsoft.com...
I haven't noticed any decreased speed, anyway. Perhaps if I start to have
thousands of transactions in the dynamic named range that the pivot table
uses, but now there are only 120 or so transactions.
But I'm also running this workbook in XL 2007 on a dual-core processor so I
think it will be a while before I run into performance issues.
Thanks again for the help.
Dave
--
Brevity is the soul of wit.