Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically update pivot tables when dynamic named range is expa
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically update pivot tables when dynamic named range is expa
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically update pivot tables when dynamic named range is
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically update pivot tables when dynamic named range is
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically update pivot tables when dynamic named range is
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically update pivot tables when dynamic named range is
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically update pivot tables when dynamic named range is
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically update pivot tables when dynamic named range is
You're welcome, and thanks for posting back to say that you got it working.
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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically update pivot tables when dynamic named range is
Hi Deb
Or, add code to the pivot table sheet module, to refresh the pivot table when you activate that sheet. That's a much better idea as it would get triggered far less frequently, and only when required.. -- Regards Roger Govier "Debra Dalgleish" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically update pivot tables when dynamic named range is
Hi Dave
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" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
automatically update pivot tables when dynamic named range is
Roger--thanks again. As for changing the trigger to the PT sheet--the pivot
table and the data table are on the same sheet. 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. "Roger Govier" wrote: Hi Dave 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
named dynamic range that ends with first text entry | Excel Worksheet Functions | |||
Dynamic named range & Offset | Excel Discussion (Misc queries) | |||
Multi-Column Dynamic Named Range...Is there an easier way? | Excel Worksheet Functions | |||
range names in pivot tables | Excel Discussion (Misc queries) | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) |