Hi.

I have a macro that tests a cell in each sheet of a workbook, then
prints only those sheets that have the right data in that cell. The
problem is that the macro tests a sheet, then prints it, then tests the
next, etc.

I need it to make the tests, compile what sheets need to be printed,
and make a single print job.

I was attempting to use 'worksheets.Array().select' and put the test
data in the array for which sheets to activate for a print session.

I think it might be better to copy the sheets that pass the test to
another (temporary) workbook, and then simple print that entire workbook
to achieve my needed result.

AmI moving in the right direction?

Code follows...

Sub selprint()
Dim i As Integer
Dim currentsheet As Worksheet

For i = 1 To ActiveWorkbook.Worksheets.Count
Set currentsheet = ActiveWorkbook.Worksheets(i)
Worksheets(i).Activate
'Skip empty sheets and hidden sheets
If Application.CountA(currentsheet.Cells) < 0 And currentsheet.Visible
Then
'change the hard-coded cell here if not F52
If (Not IsNull(Range("F52"))) And (Range("F52").Value < 0) Then
'un-comment the next line when debugging completed
ActiveSheet.PrintOut
'add comment at start of next line when debugging completed
' ActiveSheet.PrintPreview
End If
End If
Next i
End Sub

UpGrade,

Change "Correct Value" to the, not surprisingly, correct value....


Sub UpGradePrintJob()
Dim Sel As Boolean
Dim myS As Worksheet

Sel = True

For Each myS In Worksheets
If myS.Visible Then
If myS.Range("F52").Value = "Correct Value" Then
myS.Select Sel
Sel = False
End If
End If
Next myS

ActiveWindow.SelectedSheets.PrintOut

End Sub


HTH,
Bernie
MS Excel MVP


"UpGrade" wrote in message
...


Hi.

I have a macro that tests a cell in each sheet of a workbook, then
prints only those sheets that have the right data in that cell. The
problem is that the macro tests a sheet, then prints it, then tests the
next, etc.

I need it to make the tests, compile what sheets need to be printed,
and make a single print job.

I was attempting to use 'worksheets.Array().select' and put the test
data in the array for which sheets to activate for a print session.

I think it might be better to copy the sheets that pass the test to
another (temporary) workbook, and then simple print that entire workbook
to achieve my needed result.

AmI moving in the right direction?

Code follows...

Sub selprint()
Dim i As Integer
Dim currentsheet As Worksheet

For i = 1 To ActiveWorkbook.Worksheets.Count
Set currentsheet = ActiveWorkbook.Worksheets(i)
Worksheets(i).Activate
'Skip empty sheets and hidden sheets
If Application.CountA(currentsheet.Cells) < 0 And currentsheet.Visible
Then
'change the hard-coded cell here if not F52
If (Not IsNull(Range("F52"))) And (Range("F52").Value < 0) Then
'un-comment the next line when debugging completed
ActiveSheet.PrintOut
'add comment at start of next line when debugging completed
' ActiveSheet.PrintPreview
End If
End If
Next i
End Sub

On Wed, 8 Apr 2009 14:07:03 -0400, "Bernie Deitrick" <deitbe @ consumer
dot org wrote:

UpGrade,

Change "Correct Value" to the, not surprisingly, correct value....


Sub UpGradePrintJob()
Dim Sel As Boolean
Dim myS As Worksheet

Sel = True

For Each myS In Worksheets
If myS.Visible Then
If myS.Range("F52").Value = "Correct Value" Then
myS.Select Sel
Sel = False
End If
End If
Next myS

ActiveWindow.SelectedSheets.PrintOut

End Sub


HTH,
Bernie
MS Excel MVP


Thank you.

So, the "Select" function can be done sequentially without it
deslecting previously selected sheets?

I thought it was just like activate, and that a subsequent call to
"Select" would cause that to be the only sheet selected.


I ended up with a solution that hides sheets that I do not want to
print, then a manual print job of the entire workbook prints only the
non-hidden sheets. Then I close the workbook without saving, so the
sheets all 'un-hide' without a routine being required to do so.

It appears, however, that your script sets a boolean value one at a
time, and myS.Select Sel selects them.

It works just fine.

So my question is this now... If I have a macro selecting sheets, do
they remain selected even if I sequence through a macro selecting sheets,
or does it require a single select operation like you have created?

You never need to select a sheet to work with it in a macro. Instead of

Worksheets("SheetName").Select
Range("A1").Value = "Test"

use

Worksheets("SheetName").Range("A1").Value = "Test"

HTH,
Bernie
MS Excel MVP



"UpGrade" wrote in message
...
On Wed, 8 Apr 2009 14:07:03 -0400, "Bernie Deitrick" <deitbe @ consumer
dot org wrote:

UpGrade,

Change "Correct Value" to the, not surprisingly, correct value....


Sub UpGradePrintJob()
Dim Sel As Boolean
Dim myS As Worksheet

Sel = True

For Each myS In Worksheets
If myS.Visible Then
If myS.Range("F52").Value = "Correct Value" Then
myS.Select Sel
Sel = False
End If
End If
Next myS

ActiveWindow.SelectedSheets.PrintOut

End Sub


HTH,
Bernie
MS Excel MVP


Thank you.

So, the "Select" function can be done sequentially without it
deslecting previously selected sheets?

I thought it was just like activate, and that a subsequent call to
"Select" would cause that to be the only sheet selected.


I ended up with a solution that hides sheets that I do not want to
print, then a manual print job of the entire workbook prints only the
non-hidden sheets. Then I close the workbook without saving, so the
sheets all 'un-hide' without a routine being required to do so.

It appears, however, that your script sets a boolean value one at a
time, and myS.Select Sel selects them.

It works just fine.

So my question is this now... If I have a macro selecting sheets, do
they remain selected even if I sequence through a macro selecting sheets,
or does it require a single select operation like you have created?

On Thu, 9 Apr 2009 14:56:44 -0400, "Bernie Deitrick" <deitbe @ consumer
dot org wrote:

You never need to select a sheet to work with it in a macro. Instead of

Worksheets("SheetName").Select
Range("A1").Value = "Test"

use

Worksheets("SheetName").Range("A1").Value = "Test"

HTH,
Bernie
MS Excel MVP




No, but I DO need to "select" sheets if I want to generate a print job
that only prints sheets that pass the test. Otherwise, I only get a
single sheet printout, or the entire workbook. I can step through sheets,
printing them, but my requisite is that there only be one print job.

I made a hide macro that hides sheets that do not pass the test, but I
was having trouble making the print job print only selected sheets.

It would seem that a function for setting a sheet's "print flag"
would be a way MS could make this task less macro dependent, like adding
it to the sheet tab right click menu, or allowing a macro to toggle such
a flag.

WOW, I found a need that MS can incorporate! You guys should add a
print flag checkbox to the sheet tab! Than, a checkbox in the print
dialog that allows "flagged sheets only" to be printed.



"UpGrade" wrote in message
.. .
On Wed, 8 Apr 2009 14:07:03 -0400, "Bernie Deitrick" <deitbe @ consumer
dot org wrote:

UpGrade,

Change "Correct Value" to the, not surprisingly, correct value....


Sub UpGradePrintJob()
Dim Sel As Boolean
Dim myS As Worksheet

Sel = True

For Each myS In Worksheets
If myS.Visible Then
If myS.Range("F52").Value = "Correct Value" Then
myS.Select Sel
Sel = False
End If
End If
Next myS

ActiveWindow.SelectedSheets.PrintOut

End Sub


HTH,
Bernie
MS Excel MVP


Thank you.

So, the "Select" function can be done sequentially without it
deslecting previously selected sheets?

I thought it was just like activate, and that a subsequent call to
"Select" would cause that to be the only sheet selected.


I ended up with a solution that hides sheets that I do not want to
print, then a manual print job of the entire workbook prints only the
non-hidden sheets. Then I close the workbook without saving, so the
sheets all 'un-hide' without a routine being required to do so.

It appears, however, that your script sets a boolean value one at a
time, and myS.Select Sel selects them.

It works just fine.

So my question is this now... If I have a macro selecting sheets, do
they remain selected even if I sequence through a macro selecting sheets,
or does it require a single select operation like you have created?

Are Upgrade and Archimedes Lever the same person?

Jim

--
"It is the mark of an educated mind to be able to entertain a thought
without accepting it."
--Aristotle

On Thu, 9 Apr 2009 14:03:40 -0700, "RST Engineering \(jw\)"
wrote:

Are Upgrade and Archimedes Lever the same person?

Jim


You should know the answer to that already, Jim. :-)

Bernie fixed you up just right, however, so you should be set now.

I posted it in a.b.s.e