Only print worksheets with data in B2
 

Office 2003
I have created a workbook template with 31 worksheets and a main worksheet,
I would like to press a command button from the main worksheet that will
loop through all 31 sheets and send to the printer all sheets with data in
the B2 cell.

thanks in advance, David

Only print worksheets with data in B2
 

Why not put all the values on 1 sheet and print that
sheet? Try a macro like this:

Sub AllB2()
Dim ws As Worksheet
Dim i As Long
Dim iLastRow As Long

Set ws = Worksheets.Add(befo=Worksheets(1))

With ws
For i = 2 To ActiveWorkbook.Worksheets.Count
If Worksheets(i).[B2].Value < "" Then
.Cells(iLastRow + 1, "A").Value = _
Worksheets(i).Name
.Cells(iLastRow + 1, "B").Value = _
Worksheets(i).[B2]
End If
iLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Next
.Cells(1, "A").Value = "Sheet Names"
.Cells(1, "B").Value = "Value in B2"
End With

Range("A:B").EntireColumn.AutoFit

ws.PrintOut Copies:=1, Collate:=True

End Sub

---
To run, press ALT+F11, go to Insert Module, and paste
in the code. Press ALT+Q to close VBE and run the macro
under Tools Macro.

HTH
Jason
Atlanta, GA


-----Original Message-----
Office 2003
I have created a workbook template with 31 worksheets
and a main worksheet,
I would like to press a command button from the main
worksheet that will
loop through all 31 sheets and send to the printer all
sheets with data in
the B2 cell.

thanks in advance, David


.

Only print worksheets with data in B2
 

David,

If the main workhsheet has cell B2 blank, then:

Sub PrintSheets()
Dim Wks As Worksheet

For Each Wks In ActiveWorkbook.Worksheets
If Wks.Range("B2").Value < "" Then Wks.PrintOut
Next Wks
End Sub

HTH,
Bernie
MS Excel MVP

"David D" wrote in message
...
Office 2003
I have created a workbook template with 31 worksheets and a main
worksheet,
I would like to press a command button from the main worksheet that will
loop through all 31 sheets and send to the printer all sheets with data in
the B2 cell.

thanks in advance, David

Only print worksheets with data in B2
 

This was tested


Sub printb2()
For Each w In ThisWorkbook.Sheets
If w.Range("b2") < "" Then w.PrintOut Copies:=1
Next
End Sub


"David D" wrote:

Office 2003
I have created a workbook template with 31 worksheets and a main worksheet,
I would like to press a command button from the main worksheet that will
loop through all 31 sheets and send to the printer all sheets with data in
the B2 cell.

thanks in advance, David

Only print worksheets with data in B2
 

copies:=1 should be on the same line as printout
sorry


"ben" wrote:

This was tested


Sub printb2()
For Each w In ThisWorkbook.Sheets
If w.Range("b2") < "" Then w.PrintOut Copies:=1
Next
End Sub


"David D" wrote:

Office 2003
I have created a workbook template with 31 worksheets and a main worksheet,
I would like to press a command button from the main worksheet that will
loop through all 31 sheets and send to the printer all sheets with data in
the B2 cell.

thanks in advance, David