| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Pivot table refresh
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh End Sub I am using the above macro to refresh two pivot tables on the same worksheet. the problem is when I change a value on the worksheet and the macro runs, it seems to run as an endless loop. Please help. |
|
#2
|
|||
|
|||
|
Private Sub Worksheet_Change(ByVal Target As Range)
application.enableevents = false ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh application.enableevents = true End Sub This tells excel to stop looking for changes, then do the refreshing, then start looking for changes again. nc wrote: Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh End Sub I am using the above macro to refresh two pivot tables on the same worksheet. the problem is when I change a value on the worksheet and the macro runs, it seems to run as an endless loop. Please help. -- Dave Peterson |
|
#3
|
|||
|
|||
|
Thanks Dave.
I did not seem to have this problem when the pivot tables were on different worksheets. Do you still recommend I use application.enableevents = false. "Dave Peterson" wrote: Private Sub Worksheet_Change(ByVal Target As Range) application.enableevents = false ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh application.enableevents = true End Sub This tells excel to stop looking for changes, then do the refreshing, then start looking for changes again. nc wrote: Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh End Sub I am using the above macro to refresh two pivot tables on the same worksheet. the problem is when I change a value on the worksheet and the macro runs, it seems to run as an endless loop. Please help. -- Dave Peterson |
|
#4
|
|||
|
|||
|
If you have a worksheet_change event in the worksheet with that other
pivottable, you may want to use it. nc wrote: Thanks Dave. I did not seem to have this problem when the pivot tables were on different worksheets. Do you still recommend I use application.enableevents = false. "Dave Peterson" wrote: Private Sub Worksheet_Change(ByVal Target As Range) application.enableevents = false ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh application.enableevents = true End Sub This tells excel to stop looking for changes, then do the refreshing, then start looking for changes again. nc wrote: Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh End Sub I am using the above macro to refresh two pivot tables on the same worksheet. the problem is when I change a value on the worksheet and the macro runs, it seems to run as an endless loop. Please help. -- Dave Peterson -- Dave Peterson |
|
#5
|
|||
|
|||
|
Thanks again Dave.
Does this mean if I have a worksheet_change event in the worksheet but the pivottable I am refreshing is another worksheet, I do not need to use application.enableevents. "Dave Peterson" wrote: If you have a worksheet_change event in the worksheet with that other pivottable, you may want to use it. nc wrote: Thanks Dave. I did not seem to have this problem when the pivot tables were on different worksheets. Do you still recommend I use application.enableevents = false. "Dave Peterson" wrote: Private Sub Worksheet_Change(ByVal Target As Range) application.enableevents = false ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh application.enableevents = true End Sub This tells excel to stop looking for changes, then do the refreshing, then start looking for changes again. nc wrote: Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh End Sub I am using the above macro to refresh two pivot tables on the same worksheet. the problem is when I change a value on the worksheet and the macro runs, it seems to run as an endless loop. Please help. -- Dave Peterson -- Dave Peterson |
|
#6
|
|||
|
|||
|
It could.
I didn't test it, but did it start going into one of those giant loops without those statements? nc wrote: Thanks again Dave. Does this mean if I have a worksheet_change event in the worksheet but the pivottable I am refreshing is another worksheet, I do not need to use application.enableevents. "Dave Peterson" wrote: If you have a worksheet_change event in the worksheet with that other pivottable, you may want to use it. nc wrote: Thanks Dave. I did not seem to have this problem when the pivot tables were on different worksheets. Do you still recommend I use application.enableevents = false. "Dave Peterson" wrote: Private Sub Worksheet_Change(ByVal Target As Range) application.enableevents = false ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh application.enableevents = true End Sub This tells excel to stop looking for changes, then do the refreshing, then start looking for changes again. nc wrote: Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh End Sub I am using the above macro to refresh two pivot tables on the same worksheet. the problem is when I change a value on the worksheet and the macro runs, it seems to run as an endless loop. Please help. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#7
|
|||
|
|||
|
Thanks Dave.
It does not seem to go into these giant loops. "Dave Peterson" wrote: It could. I didn't test it, but did it start going into one of those giant loops without those statements? nc wrote: Thanks again Dave. Does this mean if I have a worksheet_change event in the worksheet but the pivottable I am refreshing is another worksheet, I do not need to use application.enableevents. "Dave Peterson" wrote: If you have a worksheet_change event in the worksheet with that other pivottable, you may want to use it. nc wrote: Thanks Dave. I did not seem to have this problem when the pivot tables were on different worksheets. Do you still recommend I use application.enableevents = false. "Dave Peterson" wrote: Private Sub Worksheet_Change(ByVal Target As Range) application.enableevents = false ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh application.enableevents = true End Sub This tells excel to stop looking for changes, then do the refreshing, then start looking for changes again. nc wrote: Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh End Sub I am using the above macro to refresh two pivot tables on the same worksheet. the problem is when I change a value on the worksheet and the macro runs, it seems to run as an endless loop. Please help. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#8
|
|||
|
|||
|
Then it sounds like you don't need it (now!).
nc wrote: Thanks Dave. It does not seem to go into these giant loops. "Dave Peterson" wrote: It could. I didn't test it, but did it start going into one of those giant loops without those statements? nc wrote: Thanks again Dave. Does this mean if I have a worksheet_change event in the worksheet but the pivottable I am refreshing is another worksheet, I do not need to use application.enableevents. "Dave Peterson" wrote: If you have a worksheet_change event in the worksheet with that other pivottable, you may want to use it. nc wrote: Thanks Dave. I did not seem to have this problem when the pivot tables were on different worksheets. Do you still recommend I use application.enableevents = false. "Dave Peterson" wrote: Private Sub Worksheet_Change(ByVal Target As Range) application.enableevents = false ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh application.enableevents = true End Sub This tells excel to stop looking for changes, then do the refreshing, then start looking for changes again. nc wrote: Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.PivotTables("PivotTable3").PivotCache. Refresh ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh End Sub I am using the above macro to refresh two pivot tables on the same worksheet. the problem is when I change a value on the worksheet and the macro runs, it seems to run as an endless loop. Please help. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sheet protection code conflicts with Pivot Table "auto refresh" | Excel Discussion (Misc queries) | |||
| Change Data In Pivot Table | New Users to Excel | |||
| Pivot table Data refresh | Excel Worksheet Functions | |||
| Pivot Table Problems | Excel Discussion (Misc queries) | |||
| Pivot table refresh | Excel Worksheet Functions | |||
Linear Mode
