VBA to copy selected cells down to empty cell and paste

[rioutousgnome]

Hi all

Im a new member and struggling to self teach myself VBA which i must admit i have caught the bug. but I have reached my limit

problem

I have a spreadsheet with schools names and pupils underneath, no gaps.

ie

A_____________ B ___________ C______________ D

1 St peters
2 gary___________smith _________123___________122
3 st michael
4 gary __________smith__________123___________123


i need the st peters pupils moved into a new spreadsheet called st peters with pupils underneath moved to that sheet, then the next school and so on.

currently i am highlighting the school name and dragging down till i reach next school, the only difference in the lines is next to the school is an empty cell in column B as above.

I am using this script so far which i pieced together from other posts.

Sub AddSheet()
Range("A1:E65536" & Cells(65536, 8).End(xlUp).Row).Copy
Dim strName As String
strName = Sheets(1).Range("A1")
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = strName
ActiveSheet.Paste
ActiveWorkbook.Sheets(1).Activate
Selection.Delete Shift:=xlUp
End Sub

This works fine with some manual imput but i want to automate the highlighting the schools and moving them manually till sheet 1 is empty of data.

I need to substitute the manual process of selecting the cells by having a script that will highlight from A1 all rows down till colum B is empty, then opening a new sheet and using A1 as the sheet name and moving the highlighted cells over as my script currently does.

I would be extermely grateful if someon can help me with the first part of the script.. I'm stuck..

Thanks in advance.