Solved: Split by text

[slamet Harto]

Hi expert,

Please help me on how to split some row in a sheet to another sheet.

If a cell in column B is "Saphire" then will be copied the row to the next sheet.

Please find the attached for your reference.

Thank you so much for your help.
Rgds,
Harto

[rajkumar]

Hi,
May be this one is ok with you.

it splits by the data in the first column.

Raj

[Bob Phillips]

This is a job tailor made for autofilter.

The sheet was protected, and you didn't tell us the password, so you will have to change that bit of code



Sub Spliter()
Dim LastRow As Long
Dim rng As Range

With Worksheets("Main")

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

.Unprotect Password:="?????"
.Range("B4").AutoFilter field:=2, Criteria1:="Saphire"
Set rng = .Rows(4).Resize(LastRow - 3).SpecialCells(xlCellTypeVisible)
rng.Copy Worksheets("Temp1").Range("A1")

.Range("B4").AutoFilter field:=2, Criteria1:="Ritz"
Set rng = .Rows(4).Resize(LastRow - 3).SpecialCells(xlCellTypeVisible)
rng.Copy Worksheets("Temp2").Range("A1")

.Range("B4").AutoFilter
.Protect Password:="?????"
End With

End Sub

[slamet Harto]

Dear Raj and Bob

Ups sorry. it was my fault.

The code given is worked. As always. thank you so much all.
Best, Harto