Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable.Refresh woes
Given that I have a worksheet (ws1) with two PivotTables (pt1, pt2) that rely
on data from three other worksheets (ws2, ws3, ws4) all in the same workbook. I need to be able to change data in ws2-4 and then have those changes be reflected in ws1.pt1 and ws1.pt2 . The best case scenario would be to right-click on a pivot table and then select the "! Refresh Data" option but for some reason this is greyed-out. Can someone tell me why this would be greyed-out? Other option I've tried was to add a button to each of the data worksheets (ws2--ws4) that would run the following code: Code:
Private Sub btnUpdate_Click() Dim pvtTable As PivotTable For Each pvtTable In Worksheets("ws1").PivotTables MsgBox pvtTable.Name pvtTable.RefreshTable Next pvtTable End Sub Code:
Run-time error '1004': Refresh has been disabled by a Visual Basic macro. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable.Refresh woes
After lots of trial and error, I resolved this.
Turns out both issues were caused by the fact that something somewhere was setting the PivotCache.EnableRefresh property to False for each PivotTable. Manually setting this to True in code seems to have solved both issues. "Jeremy Ragan" wrote: Given that I have a worksheet (ws1) with two PivotTables (pt1, pt2) that rely on data from three other worksheets (ws2, ws3, ws4) all in the same workbook. I need to be able to change data in ws2-4 and then have those changes be reflected in ws1.pt1 and ws1.pt2 . The best case scenario would be to right-click on a pivot table and then select the "! Refresh Data" option but for some reason this is greyed-out. Can someone tell me why this would be greyed-out? Other option I've tried was to add a button to each of the data worksheets (ws2--ws4) that would run the following code: Code:
Private Sub btnUpdate_Click() Dim pvtTable As PivotTable For Each pvtTable In Worksheets("ws1").PivotTables MsgBox pvtTable.Name pvtTable.RefreshTable Next pvtTable End Sub Code:
Run-time error '1004': Refresh has been disabled by a Visual Basic macro. Can anyone provide any insight? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA and PivotTable refresh | Excel Programming | |||
PivotTable woes | Excel Discussion (Misc queries) | |||
PivotTable After Refresh Event??? | Excel Programming | |||
refresh in pivottable | Excel Programming | |||
Refresh a PivotTable using VBA (SQL generated Pivot) | Excel Programming |