| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically Refresh pivot table
In Excel 2000, I have a spreadsheet with several table. Some of the tabs
have been filled with data from an Access MDB. I have manually created a second tab which contains a pivot table based on the data I passed in to the ther tab. The source data tab always has a different number of rows every time the Access MDB is executed. How can I automatically refresh the pivot table? Thanks -- Shell |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Automatically Refresh pivot table
What is your trigger to refresh. When you open the book or every ?? minutes?
or... There are some options in Table Options. Right click the pivot table and select Table Options. At the bottom are the refresh options (IIRC for 2000). -- HTH... Jim Thomlinson "Shell" wrote: In Excel 2000, I have a spreadsheet with several table. Some of the tabs have been filled with data from an Access MDB. I have manually created a second tab which contains a pivot table based on the data I passed in to the ther tab. The source data tab always has a different number of rows every time the Access MDB is executed. How can I automatically refresh the pivot table? Thanks -- Shell |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Automatically Refresh pivot table
When I open the pivot table tab
-- Shell "Jim Thomlinson" wrote: What is your trigger to refresh. When you open the book or every ?? minutes? or... There are some options in Table Options. Right click the pivot table and select Table Options. At the bottom are the refresh options (IIRC for 2000). -- HTH... Jim Thomlinson "Shell" wrote: In Excel 2000, I have a spreadsheet with several table. Some of the tabs have been filled with data from an Access MDB. I have manually created a second tab which contains a pivot table based on the data I passed in to the ther tab. The source data tab always has a different number of rows every time the Access MDB is executed. How can I automatically refresh the pivot table? Thanks -- Shell |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Automatically Refresh pivot table
When you open the workbook is covered. To set it at the selection of the tab
will require macro code. Not hard to do but you need to be open to using macros. Right click the sheet tab, Select view code and paste the following in the code window... Private Sub Worksheet_Activate() Dim pvt As PivotTable For Each pvt In Me.PivotTables pvt.RefreshTable Next pvt End Sub This will refresh all of the pivot tables on the tab in question. -- HTH... Jim Thomlinson "Shell" wrote: When I open the pivot table tab -- Shell "Jim Thomlinson" wrote: What is your trigger to refresh. When you open the book or every ?? minutes? or... There are some options in Table Options. Right click the pivot table and select Table Options. At the bottom are the refresh options (IIRC for 2000). -- HTH... Jim Thomlinson "Shell" wrote: In Excel 2000, I have a spreadsheet with several table. Some of the tabs have been filled with data from an Access MDB. I have manually created a second tab which contains a pivot table based on the data I passed in to the ther tab. The source data tab always has a different number of rows every time the Access MDB is executed. How can I automatically refresh the pivot table? Thanks -- Shell |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Automatically Refresh pivot table
I'm not averse to using macros. I usually write VBA code in Access.
I tried your code and finally it does not crash on an index error. I know the code is running, I put in a break point, but the pivot table did not update. I added a line in the data tab with a large numeric value. It did not show up in the pivot table. -- Shell "Jim Thomlinson" wrote: When you open the workbook is covered. To set it at the selection of the tab will require macro code. Not hard to do but you need to be open to using macros. Right click the sheet tab, Select view code and paste the following in the code window... Private Sub Worksheet_Activate() Dim pvt As PivotTable For Each pvt In Me.PivotTables pvt.RefreshTable Next pvt End Sub This will refresh all of the pivot tables on the tab in question. -- HTH... Jim Thomlinson "Shell" wrote: When I open the pivot table tab -- Shell "Jim Thomlinson" wrote: What is your trigger to refresh. When you open the book or every ?? minutes? or... There are some options in Table Options. Right click the pivot table and select Table Options. At the bottom are the refresh options (IIRC for 2000). -- HTH... Jim Thomlinson "Shell" wrote: In Excel 2000, I have a spreadsheet with several table. Some of the tabs have been filled with data from an Access MDB. I have manually created a second tab which contains a pivot table based on the data I passed in to the ther tab. The source data tab always has a different number of rows every time the Access MDB is executed. How can I automatically refresh the pivot table? Thanks -- Shell |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Automatically Refresh pivot table
The reason the pivot table did not update, even though the code ran, is that
the new data had an extra line in it. The line I added. I need the macro to account to increases or decreases in the original data (number of rows). -- Shell "Shell" wrote: I'm not averse to using macros. I usually write VBA code in Access. I tried your code and finally it does not crash on an index error. I know the code is running, I put in a break point, but the pivot table did not update. I added a line in the data tab with a large numeric value. It did not show up in the pivot table. -- Shell "Jim Thomlinson" wrote: When you open the workbook is covered. To set it at the selection of the tab will require macro code. Not hard to do but you need to be open to using macros. Right click the sheet tab, Select view code and paste the following in the code window... Private Sub Worksheet_Activate() Dim pvt As PivotTable For Each pvt In Me.PivotTables pvt.RefreshTable Next pvt End Sub This will refresh all of the pivot tables on the tab in question. -- HTH... Jim Thomlinson "Shell" wrote: When I open the pivot table tab -- Shell "Jim Thomlinson" wrote: What is your trigger to refresh. When you open the book or every ?? minutes? or... There are some options in Table Options. Right click the pivot table and select Table Options. At the bottom are the refresh options (IIRC for 2000). -- HTH... Jim Thomlinson "Shell" wrote: In Excel 2000, I have a spreadsheet with several table. Some of the tabs have been filled with data from an Access MDB. I have manually created a second tab which contains a pivot table based on the data I passed in to the ther tab. The source data tab always has a different number of rows every time the Access MDB is executed. How can I automatically refresh the pivot table? Thanks -- Shell |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Automatically Refresh pivot table
I was under the impression that the souce was Access but I guess that is not
the case. The source is actually a data set within the sheet. If the pivot was hooked to the external data then all would have been well. But as it stands we need to change the data source from a static range to a dynamic range. Check out this link on Dynamic named ranges... http://www.contextures.com/xlPivot01.html Under Dynamic Data Source... -- HTH... Jim Thomlinson "Shell" wrote: The reason the pivot table did not update, even though the code ran, is that the new data had an extra line in it. The line I added. I need the macro to account to increases or decreases in the original data (number of rows). -- Shell "Shell" wrote: I'm not averse to using macros. I usually write VBA code in Access. I tried your code and finally it does not crash on an index error. I know the code is running, I put in a break point, but the pivot table did not update. I added a line in the data tab with a large numeric value. It did not show up in the pivot table. -- Shell "Jim Thomlinson" wrote: When you open the workbook is covered. To set it at the selection of the tab will require macro code. Not hard to do but you need to be open to using macros. Right click the sheet tab, Select view code and paste the following in the code window... Private Sub Worksheet_Activate() Dim pvt As PivotTable For Each pvt In Me.PivotTables pvt.RefreshTable Next pvt End Sub This will refresh all of the pivot tables on the tab in question. -- HTH... Jim Thomlinson "Shell" wrote: When I open the pivot table tab -- Shell "Jim Thomlinson" wrote: What is your trigger to refresh. When you open the book or every ?? minutes? or... There are some options in Table Options. Right click the pivot table and select Table Options. At the bottom are the refresh options (IIRC for 2000). -- HTH... Jim Thomlinson "Shell" wrote: In Excel 2000, I have a spreadsheet with several table. Some of the tabs have been filled with data from an Access MDB. I have manually created a second tab which contains a pivot table based on the data I passed in to the ther tab. The source data tab always has a different number of rows every time the Access MDB is executed. How can I automatically refresh the pivot table? Thanks -- Shell |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| slow document / pivot table refresh and pivot function | Excel Discussion (Misc queries) | |||
| Automatically Refresh Pivot Table Data | Excel Worksheet Functions | |||
| Create refresh button in worksheet to refresh Pivot Table Data | Excel Worksheet Functions | |||
| refresh a new worsheet on pivot table refresh | Excel Worksheet Functions | |||
| Refresh Pivot Table Automatically | Excel Discussion (Misc queries) | |||
Linear Mode
