Copy part of a row to different new sheets based on text in a column

[vijanita]

Hi All,

I am looking for help with my post. I had posted this in MrExcel help forum also but did not get a reply.
Hence posting here.

I have my data in a master sheet from Row A:K. The important columns are C & K
Column C contains the Site Name and Column K has text like "Started", Completed etc.
Based on the value of text in column K, I am looking for the corresponding columns data to be copied into a new sheet (Name based on the Site Name.

I have a macro with me which copies the whole data of the corresponding data of the column to a new sheets based on the site, This is when I enter "Completed" in column K
After the data is copied I want only the datain Column A, B,C, D & K to be visible.

Hope I have been able to put this across clearly.

My code is as under :

Sub test()
Dim i As Long, e, dic As Object
Application.ScreenUpdating = False
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = 1
With Sheets("master").Range("a12").CurrentRegion
For i = 3 To .Rows.Count
If .Cells(i, 11).Value = "Completed" Then
For Each e In Split(.Cells(i, 3).Value, "/")
If Not dic.exists(e) Then
Set dic(e) = Union(.Rows(1), .Rows(i))
Else
Set dic(e) = Union(dic(e), .Rows(i))
End If
Next
End If
Next
End With
For Each e In dic
If Not IsSheetExists(e) Then Sheets.Add(after:=Sheets(Sheets.Count)).Name = e
With Sheets(e)
.Cells.Delete
dic(e).Copy
.Cells(1).PasteSpecial xlPasteColumnWidths
.Cells(1).PasteSpecial
End With
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Function IsSheetExists(ByVal txt As String) As Boolean
On Error Resume Next
IsSheetExists = Len(Sheets(txt).Name)
On Error GoTo 0
End Function