Here is the new code...it isn't filtering just right???? It prints out the
first line of everybody, instead of everything per person????

Sheets("Stepdown3").Select
Columns("B:B").Select
For Each Cell In Selection.SpecialCells(xlConstants, 23)
If Cell.Value < 0 Then
Cell.Select
With Sheets("Letter")
.Range("C13").Value = Selection.Value
.Range("E13").Value = Selection.Offset(0, -1).Value
.Range("C14").Value = Selection.Offset(0, 1).Value
.Range("I24").Value = Selection.Offset(0, 8).Value
.Range("I27").Value = Selection.Offset(0, 7).Value
.Range("I30").Value = Selection.Offset(0, 10).Value
.PrintOut Copies:=1
End With

Set sht = Sheets("Stepdown2")

topRow = sht.Range("B1").End(xlDown).Row
bottomRow = sht.Range("B65536").End(xlUp).Row
Set NameRange = sht.Range("B" & topRow & ":B" & bottomRow)


sht.AutoFilter Field:=1,
Criteria1:=Sheets("Letter").Range("C13").Value, VisibleDropdown:=False
sht.Visible = xlSheetVisible
sht.PrintOut
sht.Visible = xlSheetHidden

End If
Next Cell

"Shawn" wrote:

Ok...I am getting somewhere. This worked except it only printed one row of
the data for employee A????? She has several rows of data?

"Shawn O'Donnell" wrote:

"Shawn" wrote:
I have a hidden sheet with a table of data. Column B:B has various employee
names in it in desending alphabetical order. I would like a macro that
would, without selecting the page if possible, print out all rows of data
with employee A, then, on another sheet, print out all rows of data with
employee B, then employee C and so on.

Do you want to print this information on another worksheet or on paper?

The AutoFilter method is probably the best way to do this, but you first
have to set up a range on which to run the method.

The other thing you have to do is provide some method of selecting which of
the names in column B you want to use as the filter criterion. Maybe you
could generate a form and populate a listbox with unique entries from the
column in question.

Another problem with your plan is that you can't print from a hidden sheet.
If you really want to print from the hidden sheet, you have to temporarily
unhide it.
Here's something to get you started:

Sub JustSmith()
'Dim, Dim, Dim...
Set sht = Sheets("NameOfHiddenSheet")

topRow = sht.Range("B1").End(xlDown).Row
bottomRow = sht.Range("B65536").End(xlUp).Row
Set NameRange = sht.Range("B" & topRow & ":B" & bottomRow)
' ... or set up range some other way if you have to

' you can replace hard-coded name with text from listbox:
NameRange.AutoFilter Field:=1, Criteria1:="Smith, Tom",
VisibleDropdown:=False

Application.ScreenUpdating = False
sht.Visible = xlSheetVisible
sht.PrintPreview ' or .PrintOut
sht.Visible = xlSheetHidden
Application.ScreenUpdating = True

End Sub

If you want to copy to another sheet, you could step through each row of the
NameRegion, decide if it is not hidden (AutoFilter hides non-matching rows,)
then copy non-hidden rows to another worksheet.

--Shawn