Auto filter with protection on

[S.Limb]

Hi
Ive got an existing macro that worked brilliantly for limiting the print range and giving me multiple print copies. I now have a further requirement for the info being printed that as required the addition of a further table below the existing data. This table contains a lot of blank data that I have converted to "zeros" the idea being I could Autofilter on the range all cells that "do not contain" zeros to eliminate the blank cells.

I've managed to get a a following macro to work (by adding the highlighted red text) however when i protect the sheet the macro will not work even when the "allow autofiltering checkbox" as been ticked.

im currently using excel 2007

any help would be greatly appreciated

thanks

Sub print_3_copies()
'
' print_3_copies Macro
'
'
With Range("$A$83:$E$231")
.AutoFilter Field:=5, Criteria1:="<>0", Operator:=xlAnd
End With
If Range("b25") = 6 Then
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
ElseIf Range("b25") = 5 Then
With Rows("77:80")
.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
.Hidden = False
End With
ElseIf Range("b25") = 4 Then
With Rows("73:80")
.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
.Hidden = False
End With
ElseIf Range("b25") = 3 Then
With Rows("69:80")
.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
.Hidden = False
End With
ElseIf Range("b25") = 2 Then
With Rows("65:80")
.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
.Hidden = False
End With
ElseIf Range("b25") = 1 Then
With Rows("61:80")
.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
.Hidden = False
End With
End If
ActiveSheet.Range("$A$83:$E$231").AutoFilter Field:=5
End Sub

[Bob Phillips]

Sub print_3_copies()
'
' print_3_copies Macro
'
'
With Activesheet

.UnProtect Password:="your_password"

With .Range("$A$83:$E$231")

.AutoFilter Field:=5, Criteria1:="<>0", Operator:=xlAnd
End With

If .Range("b25") = 6 Then
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
ElseIf .Range("b25") = 5 Then
With .Rows("77:80")
.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
.Hidden = False
End With
ElseIf .Range("b25") = 4 Then
With .Rows("73:80")
.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
.Hidden = False
End With
ElseIf .Range("b25") = 3 Then
With .Rows("69:80")
.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
.Hidden = False
End With
ElseIf .Range("b25") = 2 Then
With .Rows("65:80")
.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
.Hidden = False
End With
ElseIf .Range("b25") = 1 Then
With .Rows("61:80")
.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
.Hidden = False
End With
End If

.Range("$A$83:$E$231").AutoFilter Field:=5

.Protect Password:="your_password"
End With
End Sub

[S.Limb]

Thanks that works fine for this macro. However its causing a a problem with other macros i have within the spreadsheet that are used for clearing cells that are used for data input.

The problem seems to be that when the password protection is turned back on the check boxes I normally have selected to allow the cells to be cleared are not being selected.

In addition to the standard "select locked cells" and "Select unlocked cells" I need to have the tick boxes for "format cells" and "format rows" selected when I normally protect the whorksheet within excel

Im sure this is just an option for the VBA . protect password but can't seem to find any clear help on the subject.

I wonder if anybody can help me further.

Thanks

[Bob Phillips]

You need to set one of the protection option, scenarios or objects I forget which. Record a macro that does it, and then embed that code in yours.

[S.Limb]

Thanks
Thats done the trick.
I must have left my brain on the pillow this morning.

thanks for all you help it much appreciated.