print macro

[David]

Hello


I have a workbook that has around 18 worksheets in it. Is there a macro
when clicking on the print button that only the pages with data entered
prints out.

Right now I have to print each page one at a time or print the whole
workbook even though some sheets don't have data entered.


TIA
David

 

[Don Guillett]

Sheets that have NO data do not print. Try this for testing

Sub printif()
For i = 1 To Sheets.Count
Sheets(i).PrintPreview
Next i
End Sub

 

[David]

Thanks Don but, how does this determine what worksheet has been filled in or
not? My worksheets are kind of like forms where the user answers questions.
Not all sheets get answered but there are still questions on them. I want to
create a macro that prints all pages with answers on them.

Thank you

David

 

[Dave Peterson]

Is there a cell on each sheet that must be completed?

Is it always the same address?

If no to either of these, what would you look at for each sheet to determine if
it should be printed?

 

[Don Guillett]

Tests each sheet to see if cell a1 is not empty

Sub printif()
For i = 1 To Sheets.Count
With Sheets(i)
If Not IsEmpty(.Range("a1")) Then
.PrintPreview
End If
End With
Next i
End Sub

 

[David]

Excellent Thank you both

Tests each sheet to see if cell a1 is not empty

Sub printif()
For i = 1 To Sheets.Count
With Sheets(i)
If Not IsEmpty(.Range("a1")) Then
.PrintPreview
End If
End With
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

Glad to help

 

[David]

Hello Don

I went through the whole workbook and made it so that j8 is the common cell.
I put the code you suggested in the Thisworkbook section but replaced the a1
with j8. I made a button on the first sheet that runs this macro but get a
error box with a 400 in it. Any ideas?

Thanks again

 

[Don Guillett]

I just re-tested successfully. What else did you change in the macro? You
should have just copied and pasted and only changed a1 to j8. Or, did you
re-write and forget the dots before .range and .printpreview? Post your code
or send me your workbook to the address below.