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.

Hi Dave

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").PivotC ache.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" wrote in message
...
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.

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").PivotCac he.Refresh
endsub:
Application.EnableEvents = True
End Sub
--
Brevity is the soul of wit.


"Roger Govier" wrote:

Hi Dave

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").PivotC ache.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" wrote in message
...
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.

Did you add the code to the sheet module for the source data?

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").PivotCac he.Refresh
endsub:
Application.EnableEvents = True
End Sub


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

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 a 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.


"Debra Dalgleish" wrote:

Did you add the code to the sheet module for the source data?

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").PivotCac he.Refresh
endsub:
Application.EnableEvents = True
End Sub


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

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
--
Brevity is the soul of wit.


"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 a 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.


"Debra Dalgleish" wrote:

Did you add the code to the sheet module for the source data?

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").PivotCac he.Refresh
endsub:
Application.EnableEvents = True
End Sub


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Yes, a sheet moduls is different. You can right-click on a sheet tab,
and choose View Code, then paste the code there.

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 a 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


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html