Copying data from one sheet to several others

[Charli]

Hi

I have this so far:

Private Sub CommandButton1_Click()
Dim URN As String
Dim location As String
Dim surname As String
Dim status As String

For i = 9 To 20

status = Cells(i, 10)
If status <> "SLEEPER" Then

URN = Cells(i, 1)
location = Cells(i, 6)


Sheets(location).Cells(i, 1) = URN
Sheets(location).Cells(i, 6) = location
Sheets(location).Cells(i, 10) = status

End If

Next i
End Sub


However, when it transfers the data to the new sheets, it puts them into the same row number as it was on the "Master" sheet. I am really struggling to work out how to transfer the data to the next empty available row...please help!!!

 

[VoG]

Try like this

Sheets(Location).Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = URN

 

[Charli]

Thats brilliant, thank you, you saved my computer from an open window!!