Separating large sets of data by single criteria

Question

My goal is to write a macro that will take the data (This is just a sample of what it will be like) and group each row by the shift number (Shift 1,2,3).

Essentially this sheet will become a database for data recorded in our plant so its row dimension will be dynamic.

Ideally I would be able to find a way for excel to recognize the 3 different shifts, select their respective rows and separate them with a 1 row offset where the data can be easily manipulated from there.

My initial thought was to import all of the data, filter it by each criteria, then copy and paste at an offset, but that is not working.

The code I posted below was just a test to see if the filter method would work. Any other ideas would be greatly appreciated.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Columns("A:J").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$J$51").AutoFilter Field:=3, Criteria1:="Shift 1"


Range("A8:J10").Copy
Worksheets("Sorter").AutoFilterMode = False
Range("A20").PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Columns("A:J").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$J$51").AutoFilter Field:=3, Criteria1:="Shift 2"


Range("A8:J10").Copy
Worksheets("Sorter").AutoFilterMode = False
Range("A25").PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Columns("A:J").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$J$51").AutoFilter Field:=3, Criteria1:="Shift 3"


Range("A8:J10").Copy
Worksheets("Sorter").AutoFilterMode = False
Range("A30  ").PasteSpecial (xlPasteAll)
Application.CutCopyMode = False

End Sub

UPDATE: Here is the exact code I am using now. It works very well to sort all of the data but does not seperate into groups.

Sub test02()

    Dim ws As Worksheet
    Dim aCell As Range, bCell As Range

    ~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Tracker")

    With ws
        .Columns("A:J").Sort Key1:=.Range("C1"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _`
        `DataOption1:=xlSortNormal

        Set aCell = .Columns(3).Find(What:="Shift2")

       If Not aCell Is Nothing Then .Rows(aCell.Row).Insert

         Set aCell = .Columns(3).Find(What:="Shift3")

      If Not aCell Is Nothing Then .Rows(aCell.Row).Insert
    End With
End Sub

Separating large sets of data by single criteria

1 Answers