Need Macro help

[Seanzies]

I need a macro for the following info
I have a 5 column list with a long row list that I want to take some of the info and but it into rows based on this criteria
This is all in Sheet1

Here is an example of items in the list....
A.................B
B12DST30....32
B12DST30....34
B12DST30....36
B12DST30....38
B74BRN30....30
B74BRN30....32
B74BRN32....30
B74BRN32....32
B74BRN32....34
C19BRNREG..SML
C19BRNREG..MED
C19BRNREG..LRG


I want to take all of the column A's items that are the same style and take Column B info and take the data and put it in a row format on Sheet2 starting with cell A1. Then do the same for the next item. Heres and example of what it should look like on Sheet2
A...................B.......C......D......E.......F
B12DST30......32.....34.....36....38
B74BRN30......30.....32
B74BRN32......30.....32.....34
C19BRNREG...SML...MED...LRG

Any Suggestions, there's got to be a way!!

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim ShNew As Worksheet
    Dim r As Long
    Dim c As Integer
    Dim x As Range
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("A1:A" & Sh.Range("A65536").End(xlUp).Row)
    Set ShNew = Worksheets.Add
    r = 1
    c = 2
    For Each x In Rng
        If x.Value = x.Offset(1, 0) Then
            If c = 2 Then
                ShNew.Cells(r, 1) = x.Value
                ShNew.Cells(r, c) = x.Offset(0, 1).Value
            Else
                ShNew.Cells(r, c) = x.Offset(0, 1).Value
            End If
            c = c + 1
        Else
            ShNew.Cells(r, c) = x.Offset(0, 1).Value
            r = r + 1
            c = 2
        End If
    Next x
    ShNew.Range("A1").CurrentRegion.EntireColumn.AutoFit
End Sub

 

[Seanzies]

Andrew, first, Thank you for the reply.
I tried it and when I run it all it does is do the first item and paste what's in Sheet1 cell B1 on to the new sheet cell B1.
Any Suggestions?

 

[Andrew Poulsom]

Andrew, first, Thank you for the reply.
I tried it and when I run it all it does is do the first item and paste what's in Sheet1 cell B1 on to the new sheet cell B1.
Any Suggestions?

With the sample data you posted in the range A1:B12 on Sheet1, the macro produced what you required on a new worksheet.

 

[Seanzies]

I tried it again and now it works!!!!! Thats so cool!!
Thank You!