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.
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.