Creating different sheets from data in the first sheet,

[Jo4x4]

Hi everybody! I have a very simple spreadsheet with all the data in sheet 1. I would like to split that data into different sheets. I tried vlookup, and can find the first match, but no second or third match.

Potholes	2/5/2017 <colgroup><col width="101" style="width:76pt"> </colgroup><tbody> </tbody>	John
Streetlights	8/6/ 2017	Peter
Water leaks	3/4/2017	Dave
Potholes	1/6/2017	Adam
Potholes	3/2/2017	Pete

<tbody>
</tbody>

I then want sheets named "Potholes", "Streetlights", "Water Leaks" with the Pothole sheet containing the 3 instances and names.


Thanks a stack in advance!

Jo

Windows 7, Excel 2007

 

[Fluff]

How about this

Sub ShtFltrCopy()

    Dim Dict As Object
    Dim Ky As Variant
    Dim Cl As Range
    Dim UsdRws As Long
    Dim Ws As Worksheet

Application.ScreenUpdating = False
    Set Dict = CreateObject("scripting.dictionary")

    With Sheets("Data")
        UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
    
        For Each Cl In .Range("A2:A" & UsdRws)
            If Not Dict.exists(Cl.Value) Then Dict.Add Cl.Value, Nothing
        Next Cl
    
        For Each Ky In Dict.keys
            Set Ws = Worksheets.Add
            Ws.Name = Left(Ky, 31)
            .Range("A1").AutoFilter Field:=1, Criteria1:=Ky
            .Range("A1:C" & UsdRws).SpecialCells(xlVisible).Copy Ws.Range("A1")
        Next Ky
        .AutoFilterMode = False
    End With

End Sub

Changing sheets names & ranges as required

 

[Jo4x4]

Hi, Thanks for your time and effort. Please understand that I know very little about all of this. I have changed the sheet name to 2017 which is my main data sheet. I get a "400" error??

Jo

 

[Fluff]

If you hit debug, when the error occurs, what line was highlighted?

 

[Jo4x4]

It gives a 400 error without debugging. If I try to debug line by line it stops at the very first line: Sub ShtFltrCopy()

 

[Jo4x4]

the sub seems to run, creating a new sheet, but named sheet 6,7,8 etc...

 

[Fluff]

Not sure what the error 400 is about.
Is the macro in the workbook containing the data?
Is there any sheet or workbook protection?
Are the Potholes etc in column A starting in row 2 with a header in row 1?

 

[Jo4x4]

Hi, yes, the macro is in the same workbook.
No sheet or workbook protection.
Yip, Column A starts from row 2 with the header in row 1.

Thanks again

 

[Fluff]

Strange, would you be able to post a copy of your workbook to Onedrive, or Google Drive?

 

[Jo4x4]

Hi again.

Here is the link to the file: https://drive.google.com/file/d/0B18Q7ZeBjgQheUM0UnZtV0U3VWs/view?usp=sharing

Thanks