PDA

View Full Version : hide and protected sheet macro not working



sathishsusa
01-14-2017, 05:44 AM
Hello...I have a macro to run in hide the worksheets that they protected sheet too. The macro runs a number of filters and drop down list that reside on each of the worksheets and is itself located in a module. the hide sheet if i running filters i am getting error and pivot table i cant refresh the table .Is there a way to run it on the hidden worksheets and protected sheet?. i am getting debug on the two code which is highlighted in red color kindly clear my code error and solve the problems.



Sub monthsortout()
Application.ScreenUpdating = False
Dim serchdate As Integer
SearchDate = Cells(Application.Match(CDbl(DateValue("01/" & Range("F12").Value & "/" & Year(Range("B20").Value))), Range("B:B"), 1), 2).Value
Sheets("sheet2").Visible = True
ActiveSheet.Unprotect Password:="password"
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(1, Month(SearchDate) & "/" & Day(SearchDate) & "/" & Year(SearchDate))
Sheets("sheet2").Visible = False
Sheets("sheet3").Visible = True
ActiveSheet.ListObjects("Table22").Range.AutoFilter Field:=1
ActiveSheet.ListObjects("Table22").Range.AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(1, Month(SearchDate) & "/" & Day(SearchDate) & "/" & Year(SearchDate))
Sheets("sheet3").Visible = False
ActiveSheet.Protect passowrd:="password"
Application.ScreenUpdating = True
End Sub




Sub REFRESHBUTTON()Dim Pvt As PivotTable
For Each Pvt In Sheets("sheet7").PivotTables
Pvt.RefreshTable
Next Pvt
For Each Pvt In Sheets("sheet8").PivotTables
Pvt.RefreshTable
Next Pvt
For Each Pvt In Sheets("sheet9").PivotTables
Pvt.RefreshTable
Next Pvt
For Each Pvt In Sheets("sheet10").PivotTables
Pvt.RefreshTable
Next Pvt
ActiveWorkbook.RefreshAll
End Sub

onlyadrafter
01-15-2017, 02:22 AM
Hello,

if the problem is with the sheet being hidden and/or protected, why not just unhide it and unprotect it in the code, then hide and protect again afterwards.

sathishsusa
01-15-2017, 03:07 AM
yes i tried the way of

Option Explicit

Sub monthsortout()

Const PW As String = "Secret"

ActiveSheet.Unprotect PW

'' code her

ActiveSheet.Protect PW

End Sub
but still getting error..