Printing Macro-Is there anyway I can use a macro that will print only

[DGillham]

Hi

I have a workbook with approximately 45 worksheets. Each worksheet is a weekly job list for employees. Currently we print the entire workbork, but if the employee in question has no work it simply prints off a blank sheet with just his/hers name at the top.

Is there anyway I can use a macro that will print only those worksheets that e.g. only have data in cell A2 (this would mean they have jobs for the week). We're just trying to save some paper and are too lazy to print the worksheets individually

Any help would be really appreciated.

Thanks

Darren

[stevebriz]

You can try this.

Please Login or Register  to view this content.

[colofnature]

Try this:

sub print_sheets()
dim ws as worksheet
for each ws in worksheets
if ws.[a2].value <> empty then ws.select replace:=false
next
activewindow.selectedsheets.printout copies:=1
end sub


HTH
Col

[colofnature]

Actually, my last post will print whichever sheet(s) happens to be selected when the macro is run as well as the ones you need. To avoid this, add a blank sheet to the workbook with the button to run your macro on it and use this variation:

sub print_sheets_2()
dim ws as worksheet
dim first as boolean
first = true
for each ws in worksheets
if ws.[a2].value <> empty then
if first then
ws.select replace:=true
first = false
else
ws.select replace:=false
end if
end if
next
activewindow.selectedsheets.printout copies:=1
end sub


Col

[DGillham]

Fantastic!

Thanks ever so much.

Darren