loveguy1977
02-24-2012, 01:42 PM
Dear,
How can I minimize below VBA for the following:
If
ActiveSheet.CodeName = "Sheet1" Or ActiveSheet.CodeName = "Sheet2"
................. Then
It is many and I will add more. So can you please help to list these Sheet codes in range. Say Range("B1:B100")
Thank you
Private Sub Workbook_BeforePrint(Cancel As Boolean)
On Error GoTo ErrHandler
If
ActiveSheet.CodeName = "Sheet1" Or ActiveSheet.CodeName = "Sheet2" Or
ActiveSheet.CodeName = "Sheet3" _
Or ActiveSheet.CodeName
= "Sheet4" Or ActiveSheet.CodeName = "Sheet5" Or ActiveSheet.CodeName = "Sheet6"
_
Or ActiveSheet.CodeName = "Sheet7" Or
ActiveSheet.CodeName = "Sheet8" Or ActiveSheet.CodeName = "Sheet9"
Then
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
For Each c In
Range("BB1:BB100")
If c = ""
Then
c.Select
Selection.EntireRow.Hidden = True
End
If
Next
ActiveSheet.PrintOut
Cells.Select
Selection.EntireRow.Hidden = False
Cancel = True
Application.Goto Reference:=Range("A1"), Scroll:=True
End With
End If
ErrHandler:
Application.EnableEvents = True
Application.ScreenUpdating
= True
End Sub
How can I minimize below VBA for the following:
If
ActiveSheet.CodeName = "Sheet1" Or ActiveSheet.CodeName = "Sheet2"
................. Then
It is many and I will add more. So can you please help to list these Sheet codes in range. Say Range("B1:B100")
Thank you
Private Sub Workbook_BeforePrint(Cancel As Boolean)
On Error GoTo ErrHandler
If
ActiveSheet.CodeName = "Sheet1" Or ActiveSheet.CodeName = "Sheet2" Or
ActiveSheet.CodeName = "Sheet3" _
Or ActiveSheet.CodeName
= "Sheet4" Or ActiveSheet.CodeName = "Sheet5" Or ActiveSheet.CodeName = "Sheet6"
_
Or ActiveSheet.CodeName = "Sheet7" Or
ActiveSheet.CodeName = "Sheet8" Or ActiveSheet.CodeName = "Sheet9"
Then
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
For Each c In
Range("BB1:BB100")
If c = ""
Then
c.Select
Selection.EntireRow.Hidden = True
End
If
Next
ActiveSheet.PrintOut
Cells.Select
Selection.EntireRow.Hidden = False
Cancel = True
Application.Goto Reference:=Range("A1"), Scroll:=True
End With
End If
ErrHandler:
Application.EnableEvents = True
Application.ScreenUpdating
= True
End Sub