How can I lookup and copy a section of data in a worksheet where the data row
changes from one day to the next (ie. fom a150..z300 to a250..z400) ?

In worksheet 1 in column D I need to find a name "Birmingham" and from that
row move to column A, select the range of data (a250..z400), copy, and paste
into workrksheet 2.

Your source data is assumed in Sheet1 as described,
where col D = key lookup values, eg: Birmingham

In Sheet2,
The lookup value of interest is input in A2, eg: Birmingham
Put in B2:
=OFFSET(INDIRECT("'Sheet1'!A"&MATCH($A$2,Sheet1!$D :$D,0)),ROWS($1:1)-1,COLUMNS($A:A)-1)
Copy B2 across by 26 cols, fill down by 150 rows to return the desired range*
*size is based on your "a250:z400" ie 26 cols x 150 rows
Success? hit the YES below
--
Max
Singapore
---
"Don" wrote:
How can I lookup and copy a section of data in a worksheet where the data row
changes from one day to the next (ie. fom a150..z300 to a250..z400) ?

In worksheet 1 in column D I need to find a name "Birmingham" and from that
row move to column A, select the range of data (a250..z400), copy, and paste
into worksheet 2.

try this idea

Sub FindTextCopyBlock()
Set mf = Columns("D").Find(What:="Birmingham", _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder _
:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If Not mf Is Nothing Then
Range(Cells(mf.Row, "a"), Cells(mf.Row + 150, "z")).Copy _
Sheets("sheet16").Cells(Rows.Count, 1).End(xlUp)(1)
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don" wrote in message
...
How can I lookup and copy a section of data in a worksheet where the data
row
changes from one day to the next (ie. fom a150..z300 to a250..z400) ?

In worksheet 1 in column D I need to find a name "Birmingham" and from
that
row move to column A, select the range of data (a250..z400), copy, and
paste
into workrksheet 2.