S.Limb
08-10-2011, 02:56 AM
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
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