S.Limb
07-11-2011, 03:32 AM
Hi
I'm pretty new to VBA and macros so please be gently with me.
I've managed to write a simple print macro that will hide a number of rows based on the condition of cell B25. see below While not being the most elegant of piece of code its worked fine.
Sub print_3_copies()
'
' print_3_copies Macro
'
'
If Range("b25") = 6 Then
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
ElseIf Range("b25") = 5 Then
ActiveWindow.SmallScroll Down:=36
Rows("77:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("76:81").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 4 Then
Rows("73:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("72:81").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 3 Then
Rows("69:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("68:81").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 2 Then
Rows("65:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("64:82").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 1 Then
Rows("61:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("60:81").Select
Selection.EntireRow.Hidden = False
End If
End Sub
I have then extended the print range and added to the macro applying an auto filter that will hide rows with a value 0 see below.
Sub print_3_copies()
'
' print_3_copies Macro
'
'
Range("A83:E231").Select
Selection.AutoFilter
ActiveSheet.Range("$A$83:$E$231").AutoFilter Field:=5, Criteria1:="<>0", Operator:=xlAnd
If Range("b25") = 6 Then
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
ElseIf Range("b25") = 5 Then
ActiveWindow.SmallScroll Down:=36
Rows("77:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("76:81").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 4 Then
Rows("73:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("72:81").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 3 Then
Rows("69:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("68:81").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 2 Then
Rows("65:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("64:82").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 1 Then
Rows("61:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("60:81").Select
Selection.EntireRow.Hidden = False
End If
ActiveSheet.Range("$A$83:$E$231").AutoFilter Field:=5
End Sub
While the extended code for the autofilter works fine and the print is Ok, the original code will not UNHIDE the hidden rows.
I know this must be a really simple problem but I can't see what I've done.
Any help would be much appreciated.
Thanks
I'm pretty new to VBA and macros so please be gently with me.
I've managed to write a simple print macro that will hide a number of rows based on the condition of cell B25. see below While not being the most elegant of piece of code its worked fine.
Sub print_3_copies()
'
' print_3_copies Macro
'
'
If Range("b25") = 6 Then
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
ElseIf Range("b25") = 5 Then
ActiveWindow.SmallScroll Down:=36
Rows("77:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("76:81").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 4 Then
Rows("73:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("72:81").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 3 Then
Rows("69:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("68:81").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 2 Then
Rows("65:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("64:82").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 1 Then
Rows("61:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("60:81").Select
Selection.EntireRow.Hidden = False
End If
End Sub
I have then extended the print range and added to the macro applying an auto filter that will hide rows with a value 0 see below.
Sub print_3_copies()
'
' print_3_copies Macro
'
'
Range("A83:E231").Select
Selection.AutoFilter
ActiveSheet.Range("$A$83:$E$231").AutoFilter Field:=5, Criteria1:="<>0", Operator:=xlAnd
If Range("b25") = 6 Then
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
ElseIf Range("b25") = 5 Then
ActiveWindow.SmallScroll Down:=36
Rows("77:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("76:81").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 4 Then
Rows("73:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("72:81").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 3 Then
Rows("69:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("68:81").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 2 Then
Rows("65:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("64:82").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 1 Then
Rows("61:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("60:81").Select
Selection.EntireRow.Hidden = False
End If
ActiveSheet.Range("$A$83:$E$231").AutoFilter Field:=5
End Sub
While the extended code for the autofilter works fine and the print is Ok, the original code will not UNHIDE the hidden rows.
I know this must be a really simple problem but I can't see what I've done.
Any help would be much appreciated.
Thanks