(Clayton Dool)
3/23/2007 11:53:00 PM
I have a workbook with multiple sheets. When trying to protect the
sheets, I can no longer update the data and underlying pivot tables
programmatically. How do I approach this?
Workbook
----------------------------------
- Sheet 1
- Contains Query Table linked to SQL
- (HIDDEN)
- Contains dummy cell that has formula that causes
Worksheet_Calculation to be run when query
is refreshed.
- Sheet 2, 3, and 4
- Contain Pivot Tables referencing Query Table Above
- Contain button that runs code that refreshes data.
Code/Macro
-------------------------------
Sub RefreshData()
Dim iP As Integer
Dim iW As Integer
For iW = 1 To Worksheets.Count
If (Worksheets(iW).Name = "Data") Then
Worksheets(iW).QueryTables("DataQuery").Refresh
BackgroundQuery:=False
End If
Next
End Sub
Private Sub Worksheet_Calculate()
Dim iP As Integer
Dim iW As Integer
Application.DisplayAlerts = False
Application.ScreenUpdating = False
For iW = 1 To Worksheets.Count
For iP = 1 To Worksheets(iW).PivotTables.Count
Worksheets(iW).PivotTables(iP).RefreshTable
Next
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Thanks,
Clayton