If cell in Column e = B copy next 32 rows to sheet2 then loop

[jnbrewer69]

Hi,
Thanks in advance to all you good guys and gals.

I need a macro that will look down Sheet1 column E
Find the first cell that contains "B"
Then count down 32 rows and copy the 32 rows to sheet2.

Then loop down sheet1 column E find the Next "B"

Then count down 32 rows and copy the 32 rows to sheet2.Below the previous copied rows

Then continue till the end of the sheet.

Hope that makes sense

Johnny B

 

[Von Pookie]

Not perfect but may give you some help?

Dim c As Range, firstaddress As String
Dim rngPaste As Range
With Worksheets(1).Columns(5)
    Set c = .Find("B", LookIn:=xlValues, lookat:=xlWhole)
    If Not c Is Nothing Then
        firstaddress = c.Address
        Do
            Set rngPaste = Sheets("Sheet2").Cells(Rows.Count, 5).End(xlUp).Offset(1, -4)
            .Rows(c.Row + 1 & ":" & c.Row + 32).EntireRow.Copy rngPaste
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstaddress
    End If
End With