moving rows to worksheet

terry2508

New Member
Joined
Oct 19, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,258
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
18,157
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,147,750
Messages
5,742,981
Members
423,769
Latest member
LongToast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top