PDA

View Full Version : To stop the Printing action when certain criteria is met



alexxgalaxy
10-25-2011, 09:45 PM
I worked out the VBA code that could print out a cover sheet to someone whose name was selected in a list. I've attached the file with the cover sheet and the code. The code works out fine generally but there is a problem, which is, if there is only one person indicated in the highligted area in sheet "Send to", the printing won't stop until forever and I'll kill a lot of trees. Below are the codes I worked out. It will stop after the first print out if only one person is indicated and this person doesn't appear more than once in the highlighted area. However, my codes couldn't cope if I put the same person in A2 and A3 in sheet "Send to" and then "Print". Could someone give me a hand and tell me how to stop the printing after the first print out in this secenario?

(Just added Exit Sub in the middle of the codes, which was not updated in the attach file.)


Sub CoverSheet()


Sheets("Detail List").Activate

Sheets("Detail List").Columns("A:A").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
("Details_Name"), Unique:=False

Sheets("Detail List").Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Select

Set myPrint = Selection.SpecialCells(xlCellTypeVisible)
Set myPrint = Intersect(myPrint, Columns(1))

For Each celldata In myPrint

If Range("Details_Name").Rows.Count < 3 Then

Sheets("Cover Sheet").Range("A14").Value = celldata.Row - 1
Sheets("Cover Sheet").PrintOut
Exit Sub

Else
Sheets("Cover Sheet").Range("A14").Value = celldata.Row - 1
Sheets("Cover Sheet").PrintOut
End If

Next

End Sub