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
(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