PDA

View Full Version : Help to Minimize VBA to be based in list range



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

mohanvijay
02-24-2012, 07:49 PM
Try this


Const Sht_Chk As String = ",Sheet1,Sheet2,Sheet3,Sheet4,Sheet5,Sheet6,Sheet7,Sheet8,Sheet9,"

If InStr(Sht_Chk, "," & ActiveSheet.CodeName & ",") > 0 Then

loveguy1977
02-25-2012, 09:57 AM
Thank you
It is working

I would appreciate if can be list in column A. If can't then it is ok

Thank you again