Creating new 'sheet' based on data in a cell

[dmfweb]

I don't THINK this can be done, but I have seen some pretty amazing things here.

I have some data like below:

Field 1	Location	Field 3	Field 4
abc	Location ABC	23214	46436
2323	Location DEF	3434	464364
2323	Location GHI	33431	13446534
13213	Location DEF	343	32432

Is it possible to create a new sheet per location and all the lines with that location would be on it. The data is about 15,000 lines with close to 50 locations. I know it can be done in a pivot table and then I could click on each grouping which will create a new sheet, but I would like this done simpler. IF possible...

Appreciate you!

 

[mumps]

Try:

Sub CreateSheets()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, dic As Object, srcWS As Worksheet
    Set srcWS = Sheets(1)
    v = srcWS.Range("B2", srcWS.Range("B" & Rows.Count).End(xlUp)).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v) To UBound(v)
        If Not dic.exists(v(i, 1)) Then
            dic.Add v(i, 1), Nothing
            srcWS.Range("A1").CurrentRegion.AutoFilter 2, v(i, 1)
            Sheets.Add after:=Sheets(Sheets.Count)
            ActiveSheet.Name = v(i, 1)
            srcWS.AutoFilter.Range.Copy Range("A1")
        End If
    Next i
    srcWS.Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub