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
However this results in the following error
Code:
Run-time error '1004':
Refresh has been disabled by a Visual Basic macro.
Can anyone provide any insight?