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

  • Thread starter Thread starter Don
  • Start date Start date
D

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.
 
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
 
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
 
Back
Top