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.