For sample, the code below copies all the rows in the "Materlist" sheet that contains "Brooklyn" on column E to the sheet named "Brooklyn" in the same workbook. I have a sheet named for each value on column E of the Masterlist, about 35, and would like to separate the data to it's own sheet when new data is enter in the master list. The masterlist gets updated by several people, but I have to separate the data to it's own sheet, so I am trying to make the process more efficient. I already have a code to create a new sheet for every unique value in column E of the master list, now I want it to move that data to the sheet it created. The code below works but it is not automatic. How can I make it so it moves data(entire row) for all values on column E to the sheet named after that value.
Sub Cheezy()
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
I = Worksheets("Masterlist").UsedRange.Rows.Count
J = Worksheets("Brooklyn").UsedRange.Rows.Count
If J = 1 Then
IF Application.WorksheetFunction.CountA(Worksheets("Brooklyn").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Masterlist").Range("E1:E" & I) On Error Resume Next
Application.ScreenUpdating = False
For Each xCell In xRg If CStr(xCell.Value) = "Brooklyn" Then xCell.EntireRow.Copy Destination:=Worksheets("Brooklyn").Range("A" & J + 1) xCell.EntireRow.Delete J = J + 1
End If
Next Application.ScreenUpdating = True
End Sub
Bookmarks