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