Only print worksheets with data in B2

[David D]

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

 

[Jason Morin]

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(before:=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

 

[Bernie Deitrick]

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

 

[Guest]

This was tested


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

 

[Guest]

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