Maco to copy data from wks to wks

[Guest]

Hi All,

I need a maco that would look down column b and at each change of data copy
the previous rows to a new sheet.

John books
John books
Kate puzzles
Kate puzzles

The macro would copy the first two rows to a new sheet and then the next tow
rows to a new sheet ect.

Any help would be greatly appreciated.

 

[Guest]

Tammy,

Maybe something like this:

Sub test()

Dim lLastRow As Long
Dim rngCopy As Range
Dim s As Worksheet

Dim c As Range

lLastRow = UsedRange.SpecialCells(xlCellTypeLastCell).Row

For Each c In Range("B1:B" & lLastRow)
If rngCopy Is Nothing Then
Set rngCopy = c
Else
Set rngCopy = Application.Union(rngCopy, c)
End If
If c.Value <> c.Offset(1).Value Then
Set s = ThisWorkbook.Worksheets.Add(After:=Sheets(Sheets.Count))
rngCopy.EntireRow.Copy s.Range("A1")
Set rngCopy = Nothing
End If
Next c


End Sub