need a new print funtion

[niyrho]

I'm trying to work out a code the will print only pages that have things entered in them. Something like:

on sheet 1
if d2 = "" then skip
else print page 1

id d55 = "" then skip
else print page 2

and so on

I've been trying to use "if" and "then" funtions to do it, but I can't get them to work in vba. Anyone have any better ideas, or an example to get me started?

[mdmackillop]

[VBA]Sub Prints()
Dim sh As Worksheet
For Each sh In Sheets
Select Case sh.Index
Case 1
If Not sh.Range("D2") = "" Then sh.PrintOut
Case 2
If Not sh.Range("D5") = "" Then sh.PrintOut
Case 3
If Not sh.Range("A1") = "" Then sh.PrintOut
End Select
Next
End Sub[/VBA]

[niyrho]

How do I get that code to work off of a command button on a different sheet?

[mdmackillop]

[VBA]Private Sub CommandButton1_Click()
Prints
End Sub
[/VBA]

[niyrho]

Thats not workin, all I get is a compile error.

[mdmackillop]

What type of button are you adding? Forms or ActiveX

[niyrho]

Forms I think.

[mdmackillop]

Assign the macro to the button

[niyrho]

That still isn't working at all. I have it set up like:

Sheet1 is a inspection report and has the "print" button on it.
Sheet1 always gets printed.

Sheet2 is the device list and is 16 pages long. We don't always use all 16 pages. Sometimes we only use 1 of them so I don't want it to print all 16 every time.

I need a code that will, on printbutton click(), print sheet1 and print only the pages of sheet2 that have been filled out.

I can modify sheet2 if need be. But I have to keep this thing user friendly and idiot proof.

[mdmackillop]

This assumes regular location of data.

[VBA]
Private Sub CommandButton1_Click()
Dim sh As Worksheet
Dim i As Long, x As Long
Sheets(1).PrintOut

With Sheets(2)
For i = 2 To 800 Step 50
x = x + 1
If .Cells(i, 4) <> "" Then
.PrintOut from:=x, to:=x
End If
Next
End With
End Sub

[/VBA]

[niyrho]

That code was perfect. Not really sure how "x" works, but that was exactly what I needed. Thanks!!!