nick612hayden
New Member
- Joined
- May 15, 2012
- Messages
- 33
Can you direct me as to why?
Would be much appreciated!
Nick
Sub ALL()
Dim wBook As Workbook
Dim wSheet As Worksheet
Dim Pwd As String
Pwd = "password"
Application.ScreenUpdating = False
For Each wBook In Workbooks
'Unprotect all sheets at once
For Each wSheet In wBook.Worksheets
wSheet.Unprotect Password:=Pwd
Next wSheet
'Refresh all pivot tables
For Each wSheet In wBook.Worksheets
Sheets("Sheet1").Select
ActiveSheet.PivotTables("PivotTable4").RefreshTable
ActiveSheet.PivotTables("PivotTable5").RefreshTable
Sheets("Sheet2").Select
ActiveSheet.PivotTables("PivotTable1").RefreshTable
Sheets("Sheet3").Select
ActiveSheet.PivotTables("PivotTable3").RefreshTable
'Protect all sheets at once
For Each wSheet In wBook.Worksheets
wSheet.Protect Password:=Pwd, AllowFiltering:=True, _
AllowFormattingColumns:=False, AllowFormattingRows:=True, AllowUsingPivotTables:=True
Next wSheet
Next wBook
Application.ScreenUpdating = True
End Sub
Would be much appreciated!
Nick
Sub ALL()
Dim wBook As Workbook
Dim wSheet As Worksheet
Dim Pwd As String
Pwd = "password"
Application.ScreenUpdating = False
For Each wBook In Workbooks
'Unprotect all sheets at once
For Each wSheet In wBook.Worksheets
wSheet.Unprotect Password:=Pwd
Next wSheet
'Refresh all pivot tables
For Each wSheet In wBook.Worksheets
Sheets("Sheet1").Select
ActiveSheet.PivotTables("PivotTable4").RefreshTable
ActiveSheet.PivotTables("PivotTable5").RefreshTable
Sheets("Sheet2").Select
ActiveSheet.PivotTables("PivotTable1").RefreshTable
Sheets("Sheet3").Select
ActiveSheet.PivotTables("PivotTable3").RefreshTable
'Protect all sheets at once
For Each wSheet In wBook.Worksheets
wSheet.Protect Password:=Pwd, AllowFiltering:=True, _
AllowFormattingColumns:=False, AllowFormattingRows:=True, AllowUsingPivotTables:=True
Next wSheet
Next wBook
Application.ScreenUpdating = True
End Sub