Macro to find a word and select a range based in their position

[jongova]

I have a format that is 62 rows by 11 columns and in the same sheet and are placed one above the other. Each format has an identifier like "Eco 2" and their position in in the first column, row number 4 of each format.

I want a macro that find for example "Eco 4". If the Eco 4 are in the cell A314 the macro should select the range A311:K372, then copy and paste in other workbook, each format in different sheet of the new workbook. Is possible that I can found Eco 4 more than one time in the original workbook.

I want consolidate all Eco 4 formats in a new worbook in differents sheets.


Thanks experts.

In the attached file the identifier is "Pozo: XXXX" try searching CAFETO-20 (DES)

[ashleyuk1984]

Can you upload a copy of your workbook so that we can see were working with :) It's easier that way, as we can code and debug on a 'true' example :)

[snb]

sub M_snb()
for j =1 to sheets("reportd").usedrange.rows.count\62
sheets("reportd").usedrange.find("CAFETO-20",sheets("reportd").cells(62*(j-1)+1,1),xlvalues,xlpart).offset(-3).resize(62,11).copy sheets("CAFETO-20").cells(rows.count,1).end(xlup).offset(2)
next
End Sub
or


Sub M_snb()
With Sheets("reportd").UsedRange
.AutoFilter 1, "*CAFETO-20*"
c00 = .Columns(1).SpecialCells(12).Address
.AutoFilter

For Each cl In Range(c00)
If cl.Row > 1 Then cl.Offset(-3).Resize(62, 11).Copy Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(2)
Next
End With
End Sub