Skip a worksheet if certain letter is not found in a column

lar48ry

Board Regular
Joined
Feb 4, 2005
Messages
53
I have a several macros that will print the contents of a sheet if a certain character is found in column "I". The problem is that it will print the first 5 rows of that sheet, I want to skip over that sheet instead. I have looked for a method to test for the letter but I seem to come up blank, GRRRRRRRR.

The following is an example of my code:

Sub PRINTSTATUS_OPEN()
'
' Macro recorded 2/7/2005 by Larry A. Patzer
' TO PRINT JUST THOSE ROWS THAT HAVE A STATUS OPEN
' RANGE OF ROWS TO BE USED IS FROM A1 TO A400
'
' To prevent flickering during run
Application.ScreenUpdating = False
Set rngActiveCell = ActiveCell

'Stores active cell on current sheet
Set rngActiveCell = ActiveCell

Dim SheetsToPrint()
Dim iSHEET

SheetsToPrint = Array(Sheets("INFORMATION"), Sheets("DESIGN"), _
Sheets("VALIDATION"), Sheets("PRODUCTION"), Sheets("POST_LAUNCH"))
For Each iSHEET In SheetsToPrint
' SELECTS THE INFORMATION TO BE PRINTED
iSHEET.Activate
ActiveSheet.UsedRange.Select
Range("A5:I" & Selection.Rows.Count).Select
Selection.AutoFilter Field:=9, Criteria1:="=O", Operator:=xlAnd

If IsEmpty(iSHEET.Range("A6")) Then GoTo 100
' PRINTS THE FIRST 5 ROWS AND COLUMNS A THROUGH I ON EACH PAGE
' This Sub sets the print requirements
PRINTPAGESETUP

' VIEW SELECTION - THIS IS SET TEMPORARILY TO PROVE SUB
' TO BE REMOVED
Selection.PrintPreview

' TO BE UNCOMMENTED WHEN SUB WORKS
' Selection.PrintOut Copies:=1, Collate:=True

' TURNS AUTOFILTER OFF AFTER PRINT REQUIREMENT
100 Selection.AutoFilter
[A65536].End(xlUp).Select
Next iSHEET

Application.ScreenUpdating = True
'Returns to active cell on sheet when macro invoked
Application.Goto rngActiveCell

End Sub

This routine will skip over a sheet that has no data in it (if sheet is in use it must have a number in 'A6')

Thanks in advance.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

lar48ry

Board Regular
Joined
Feb 4, 2005
Messages
53
I have continued to work on this macro. I have created a For loop that appears to work, however, it does not step thru all the sheets as expected. Maybe someone can spot the errors of my ways. I am including the macro with asterisks to indicate the added code.

Sub PRINTSTATUS_OPEN()
'
' Macro recorded 2/7/2005 by Larry A. Patzer
' TO PRINT JUST THOSE ROWS THAT HAVE A STATUS OPEN
' RANGE OF ROWS TO BE USED IS FROM A1 TO A400
'
' To prevent flickering during run
Application.ScreenUpdating = False
Set rngActiveCell = ActiveCell

'Stores active cell on current sheet
Set rngActiveCell = ActiveCell

Dim SheetsToPrint()
Dim iSHEET

SheetsToPrint = Array(Sheets("INFORMATION"), Sheets("DESIGN"), _
Sheets("VALIDATION"), Sheets("PRODUCTION"), Sheets("POST_LAUNCH"))
For Each iSHEET In SheetsToPrint

* 'To skip sheet if there are no Open Issues
* For Each Cell In Range("I6")
* If Cell.Text = "O" Then GoTo 50
* If Cell.Text = "" Then GoTo 100
* Next Cell

' SELECTS THE INFORMATION TO BE PRINTED
*50 iSHEET.Activate
ActiveSheet.UsedRange.Select
Range("A5:I" & Selection.Rows.Count).Select
Selection.AutoFilter Field:=9, Criteria1:="=O", Operator:=xlAnd

If IsEmpty(iSHEET.Range("A6")) Then GoTo 100
' PRINTS THE FIRST 5 ROWS AND COLUMNS A THROUGH I ON EACH PAGE
' This Sub sets the print requirements
PRINTPAGESETUP

' VIEW SELECTION - THIS IS SET TEMPORARILY TO PROVE SUB
' TO BE REMOVED
Selection.PrintPreview

' TO BE UNCOMMENTED WHEN SUB WORKS
' Selection.PrintOut Copies:=1, Collate:=True

' TURNS AUTOFILTER OFF AFTER PRINT REQUIREMENT
*100 Selection.AutoFilter
[A65536].End(xlUp).Select
Next iSHEET
'Resets screen updating
Application.ScreenUpdating = True
'Returns to active cell on sheet when macro invoked
Application.Goto rngActiveCell

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,572
Members
414,389
Latest member
MarkElla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top