Move row if specific word is found in row of text

[Muppett]

Hi all

I am fairly new to VBA and trying to move rows if a certain word is found in a cell in a row of text.

So ie:

Column E has a title of Known As - this column then has names of premises such as "Made up name school".

I need a way of moving every row to another sheet when this "school" is found...

VOICRENT to VOICRENTSCHOOLS

I have a HUGE spread sheet and simply filtering copy paste is gonna take FAR longer then coding..

 

[Joe4]

If you use Advanced Filters, you can set it up so that the Filtered results are automatically copied to a different sheet.
See: https://www.extendoffice.com/documents/excel/4189-excel-dynamic-filter-to-new-sheet.html
There are also YouTube tutorials which show you how to do this.

 

[Muppett]

I was thinking something more like this to be honest.. but need it to see school in a row of text in one column... now teh below works fine BUT I would have to have a column that has School in each cell that i needed moving which defeats what im trying to achieve..

Sub School()


Set i = Sheets("VOICRENT")
Set e = Sheets("VOICRENTSCHOOLS")
Dim d
Dim j
d = 1
j = 2


Do Until IsEmpty(i.Range("E" & j))


If i.Range("E" & j) = "School" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value


End If
j = j + 1
Loop


End Sub

 

[Joe4]

Ah, so the issue is you want to physically move the records out of the original sheet to the new sheet. Is that correct?

Can you post a small data sample to show what the data you are working with looks like?
And if you want the "archived" list to look any different from your original (as far as columns go), please post an example of that as well.