moving rows to worksheet

[terry2508]

Hi Guys,

This may end up being really simple, but ive hit a wall with this.

i have a worksheet of data, with ambulance call signs. Is it possible for excel to search if the call sign is in the raw data, if it finds any of the call signs then it moves all of the information contained within the row, related to that call sign, to a seperate worksheet?

the downside is there are 109 different call signs that could be within the data so it would need to search for all of them in one go?

if anyone could help it would be greatly appreciated.

Thank you

Terry

 

[FryGirl]

Hi Terry,

Maybe this will help you, but it is requiring VBA. When you run this macro, it will ask you to click on the column that you want to split your data by.

Sub SplitOutSheets1()
    Dim LastRow As Long
    Dim iStart  As Long
    Dim iEnd    As Long
    Dim i       As Long
    Dim LastCol As Long
    Dim iCol    As Integer
    Dim ws      As Worksheet
    Dim r       As Range
    On Error Resume Next
    Set r = Application.InputBox("Click in the column to extract by", Type:=8)
    On Error GoTo 0
    If r Is Nothing Then Exit Sub
    iCol = r.Column
    Application.ScreenUpdating = False
    With ActiveSheet
        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        .Range(.Cells(2, 1), Cells(LastRow, LastCol)).Sort Key1:=Cells(2, iCol), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        iStart = 2
        For i = 2 To LastRow
            If .Cells(i, iCol).Value <> .Cells(i + 1, iCol).Value Then
                iEnd = i
                Sheets.Add After:=Sheets(Sheets.Count)
                Set ws = ActiveSheet
                On Error Resume Next
                ws.Name = .Cells(iStart, iCol).Value
                On Error GoTo 0
                ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
                With ws.Rows(1)
                    .HorizontalAlignment = xlCenter
                    With .Font
                        .ColorIndex = 5
                        .Bold = True
                    End With
                End With
                .Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2")
                iStart = iEnd + 1
            End If
        Next i
    End With
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub

 

[My Aswer Is This]

Terry. I see you just joined the forum today so glad to see you here.
So to help with any postings we need specific details.
Like we need to know the name of your sheet we are working with
Like you said:
i have a worksheet of data
But did not say the name of the worksheet
And you said:
with ambulance call signs
But did not say where these call signs are
Are they in column A of your sheet or column P
Say column A Or P do not say column call signs

And you said:
then it moves all of the information contained within the row, related to that call sign, to a seperate worksheet?
What is the name of this seperate worksheet?

You said:
the downside is there are 109 different call signs

where are these 109 call signs
See we need specific details to help you.