Find Keyword and Paste in Worksheet with same name

[leeksleeks]

Hi,

I currently have a macro that searches a whole list of sports results that i have already imported (column A:F) and when it finds the name of a team it then pastes that information onto a worksheet with the same name (First 3 letters), whether the team be home or away. It does however only paste the information in a selected cell number. What I would like it to do is to paste these results into the next available blank cell on that worksheet. What I would like it to do is to paste the result where the home team appears first into the next blank cell in Column A and if they are the away team to paste the information into the next blank cell in column F.

This is how it would look for example (Each cell is represented by |####|

|07/11/13| |Team A| |29| |-| |10||Team B| would be pasted into the next blank cell of Column A of Team A's worksheet
|16/11/13| |Team B| |35| |-| |7||Team A| would be pasted into the next blank cell of Column S of Team A's worksheet.

The code I currently use is:

Sub updateresults()
Dim home As String
Dim away As String
Dim name As String
Dim index As Integer

For k = 4 To 16 'this is the number of teams worksheets

index = 0

For i = 1 To 500

home = Trim(Cells(i, 2).Value)
away = Trim(Cells(i, 4).Value)
name = Trim(Sheets(k).name)

If home = name Or away = name Then
For j = 1 To 5
Sheets(k).Cells(3 + index, j) = Cells(i, j)
Next j
index = index + 1
End If

Next i

Next k


End Sub

Any ideas as how to solve this will be greatly appreciated. Good luck!!!