How to look up and copy a changing range of data ?

[Don]

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.

 

[Max]

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

 

[Don Guillett]

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