Printing Worksheet only if data exists

O

oakman

Greetings,

I have a list with about 36 names on Sheet 1. (Each of these name
corresponds to a sheet with the same name in the workbook.) Next t
each name on the list, I have entered a formula to show the wor
"PRINT" if any data is present on that sheet or leave the cell blank i
no data is present. I am currently using a For Each ... Then loop t
scan the aforementioned range of cells so that if the word "PRINT
appears next to the sheet name, the corresponding worksheet shoul
print. However, I cannot figure out how to tell the macro to print th
particular worksheet once it finds the word "PRINT". Any pointers ar
very much appreciated. Please help!

Thanks in advance!

Sub PrintMac()
For Each c In Worksheets("Sheet1").Range("SALES").Cells
If c.Value = "Print" Then
Worksheets
With c.Font
.Bold = True
.Italic = True
End With
End If
Next c


End Su
 
S

SOS

Hi oakman,

I altered your code slightly and I was able to get it to print th
required sheets. See below:


Sub PrintMac()
For Each c In Worksheets("Sheet1").Range("SALES").Cells
If c.Value = "Print" Then
Sales_Sht_Name = c.Offset(0, -1).Value
With Worksheets
With c.Font
.Bold = True
.Italic = True
End With
Worksheets(Sales_Sht_Name).PrintOut Copies:=1, Collate:=True
End With
End If
Next c
End Sub


Regards

Seamu
 
O

oakman

Thank you so much Seamus!

This slight change has made a big difference.

Regards

Oakman.
 
D

Don Guillett

My excel 2002 will not print any sheet without data so your print column is
unnecessary. This will print all but sheet1 and the worksheets that do not
have anything to print.

Sub printem()
For Each ws In Worksheets
If ws.Name <> "Sheet1" Then ws.PrintPreview
Next
End Sub
 
Top