a loop formula that filters and prints?

[Guest]

I have a table of data in Sheet1. Column A:A lists employee's names. I have
a macro that will take the data in Sheet1 Column A:A and past it in Sheet2
Column A:A, it then sorts it in alpha order and filters it to unique values.

I need a looping formula that will go to Sheet2 Cell A1 and then filter the
data table in Sheet1 Column A:A by this value and then print. I then need
the code to loop to Sheet2 Cell A2 and do the same filter and print on Sheet1
etc. I need this to loop until the first empty cell.

Or, some other method that would work. Please help. Thanks in advance.

 

[Guest]

Based on your givens, a loop like this should work.

Dim strEmpName As String
Sub Print_Records()
Sheet("Sheet2").Select
Range("A1").Select
Do Until ActiveCell.Value = ""
strEmpName = ActiveCell.Value
Sheets("Sheet1").Select
Range("A2").Select
Selection.AutoFilter Field:=1, Criteria1:=strEmpName
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("Sheet2").Select
ActiveCell.Offset(1, 0).Select
Loop

End Sub

 

[Guest]

Selecting items around an app is not advisable and slows things down.
Here's code that will accomplish the task without selecting:

Sub Print_Records()
Dim oCell As Range
Dim EmplList As Range
Dim i As Integer

Set EmplList = Sheets("Sheet2").Range("A1:A" & Cells(Rows.Count,
1).End(xlUp).Row)
i = 2

For Each oCell In EmplList
With Sheets("Sheet1")
.Range("A" & i).AutoFilter Field:=1, Criteria1:=oCell.Value
.PrintOut Copies:=1
.Range("A" & i).AutoFilter
End With
i = i + 1
Next oCell

End Sub