Copying cells between two points

[madbloke]

Ok, I might explain this horribly (apologies if so).

What I've got is a sheet with a list of areas, then under each area, a list of teams and a total

Example

Area30

Team1 - Count 4
Team2 - Count 7
Team3 - Count 5

Sum - 16

Area 36

Team1 - Count 2
Team4 - Count 6

Sum - 8

etc, etc.


I need to copy parts of this sheet to another workbook, so for example, I want to find 'Area 36', and copy everything from the words 'Area 36' down to the 'Sum' underneath it.

I've figured out how to search for the areas I want, but can't get my head around finding out where the 'Sum' underneath it is.

Can anyone help? Happy to try and explain better if this is nonsense!

Public Sub findstuff()
Dim num As String
Dim lastline As String
Dim leftbit As String
Dim area As String

'open the figures sheet

Path = ThisWorkbook.Path & "\"
Workbooks.Open Path & "figures.xls"


Workbooks("otherbook.xls").Worksheets("Sheet1").Range("A3:S3000").Clear
num = 3
lastline = Workbooks("figures.xls").Worksheets("Summary").Range("B65536").End(xlUp).Row
For I = 3 To lastline

area = Workbooks("figures.xls").Worksheets("Summary").Cells(I, 2).Value



If area = "Area36" Or area = "Area32" Then

Workbooks("figures.xls").Worksheets("Summary").Range("A" & _
I & ":S" & I).Copy Destination:= _
Workbooks("otherbook.xls").Sheets("Sheet1").Range("A" & num)
num = num + 1

End If
Next I

End Sub