Moving data between worksheets

[Joe]

Hello,

I have a workbook that tracks each individual's completed reports. I have a
master worksheet and one worksheet per employee.

Once a day I have to auto filter the master list by name and copy and paste
all of that individual's reports in their respective worksheets.

How would I go about writing a macro to match each individual's name and
copy the row it to their own respective worksheet? Each Individual worksheet
has been renamed to the individual's last name (which matches the name field
in the master sheet)

Any help is sure appreciated.

Thank You.

-Joe

[crispbd]

Sub MoveReports()
Dim MasterSheet As Object
Dim EmployeeSheet As Object
Dim RowCounter As Long
Dim EmployeeColumn As Integer
Dim EmployeeName As String
EmployeeColumn = 1 ' change this to the column number of the employee name
Set MasterSheet = ThisWorkbook.Sheets("MasterSheet")
For RowCounter = 1 To MasterSheet.UsedRange.Rows.Count 'If data starts in row 2, change the value of 1
EmployeeName = MasterSheet.Cells(RowCounter, EmployeeColumn).Value
Set EmployeeSheet = Sheets(EmployeeName)
MasterSheet.Select
MasterSheet.Cells(RowCounter, EmployeeColumn).EntireRow.Select
Selection.Copy
EmployeeSheet.Select
EmployeeSheet.Cells(GetLastRow(EmployeeName), 1).Select
ActiveSheet.Paste
Next RowCounter

End Sub


Function GetLastRow(ByVal SN As String) As Long
Dim RowCount As Long
Do
RowCount = RowCount + 1
Loop While Sheets(SN).Cells(RowCount, 1).Value <> ""
GetLastRow = RowCount
End Function