hide and protected sheet macro not working

sathishsusa · Jan 14th 2017

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.

one thing i have posted this problems in other forums too see the below link so if u see cross post pls avoid it.
http://www.excelforum.com/show…763&p=4560211#post4560211
http://www.vbaexpress.com/foru…rking&p=354962#post354962

Code

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"
[B][COLOR=#ff0000]    ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1[/COLOR][/B]
    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

Display More

Code

Sub REFRESHBUTTON()Dim Pvt As PivotTable
    For Each Pvt In Sheets("sheet7").PivotTables
[B][COLOR=#ff0000]        Pvt.RefreshTable[/COLOR][/B]
            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

Display More

sathishsusa · Jan 14th 2017

Re: hide and protected sheet macro not working

anyone have idea to solve this ..... pls

**royUK** · Jan 15th 2017

Re: hide and protected sheet macro not working

When using Cide Tags do not mix them with other Tags, it makes it difficult to copy the code.

When using a protected sheet the usual method is to unprotect then protect the sheet, you need to do this with the Refresh code.

Code

Option Explicit


Sub Test()
    Const PW As String = "Secret"


    ActiveSheet.Unprotect PW


    '' your code her


    ActiveSheet.Protect PW


End Sub

Display More

Probably a better way is to Protect with UserInterFace only

sathishsusa · Jan 15th 2017

Re: hide and protected sheet macro not working

hi,
thanks for the code ...
its work for refresh button but same thing i tried in monthsortout macro its getting error..
i tried in other way i getting the error on this can u let me know whats wrong in this..

Code

Option Explicit
Const PW As String = "secret"
Sub monthsortout()
Dim searchdate As Long
searchdate = Cells(Application.Match(CDbl(DateValue("01/" & Range("F12").Value & "/" & Year(Range("B20").Value))), Range("B:B"), 1), 2).Value
With Sheet2
.Unprotect PW
    .Visible = xlSheetVisible
    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))
    .Protect PW
    End With
searchdate = Cells(Application.Match(CDbl(DateValue("01/" & Range("F12").Value & "/" & Year(Range("B20").Value))), Range("B:B"), 1), 2).Value
With Sheet7
        .Unprotect PW
        .Visible = xlSheetVisible
        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))
    .Protect PW
    End With
End sub

Display More

in this if i run the macro i getting error on the sheet7 has" ActiveSheet.ListObjects("Table22").Range.AutoFilter Field:=1" showing "subscript out of range".
when i run this vb using f8 the sheet 2 is ok but getting error on the sheet7 so what wrong on my code any suggestion please....

NoSparks · Jan 15th 2017

Re: hide and protected sheet macro not working

For me, making a sheet visible does not automatically make it the active sheet. Does it for you ?

sathishsusa · Jan 15th 2017

Re: hide and protected sheet macro not working

Yes correct.... My friend

**royUK** · Jan 16th 2017

Re: hide and protected sheet macro not working

Use the InterFace example or don't use ActiveSheet, use the sheet name

hide and protected sheet macro not working

Participate now!

Share