Micro for splitting by 1 column into multiple tabs on the same wordbook

[KellieMeehan]

Hi,

I have an excel spreadsheet and would like a micro for splitting the data into multiple tabs using the data in just 1 column and then for the macro to name the tab by what is in the splitting column, hope this makes sense, I would like the column in yellow to be the column that splits the data

 

[Fluff]

That message pretty much sums it up. The value you are trying to use as the sheet name is not valid.

 

[mumps]

It looks like the column you are using has some reserved characters or blank cells as described in the error message. Have you checked the data in the desired column?

 

[KellieMeehan]

Did what you said and now this has come up:-

 

[mumps]

Please use the XL2BB add-in to post a screenshot (not a picture) of your actual sheet (de-sensitized if necessary).

 

[vcoolio]

@mumps:

I hope you don't mind, but I've 'butchered' your code as follows:-

Sub CreateSheets()

    Application.ScreenUpdating = False
    
    Dim v As Variant, i As Long, ws As Worksheet, x As String, ColToFilter
    Set ws = ActiveSheet
    x = InputBox("Please enter a Column letter.")
    ColToFilter = InputBox("Which Column number?")
    If x = vbNullString Then Exit Sub
    If ColToFilter = vbNullString Then Exit Sub
    
    v = ws.Range(x & 2, ws.Range(x & Rows.Count).End(xlUp)).Value
    
    For i = LBound(v) To UBound(v)
          With ws
                 .Range("A1").CurrentRegion.AutoFilter ColToFilter, v(i, 1)
                 .AutoFilter.Range.Copy
                  If Not Evaluate("ISREF('" & CStr(v(i, 1)) & "'!A1)") Then
                  Sheets.Add(After:=Sheets(Sheets.Count)).Name = v(i, 1)
                  Range("A1").PasteSpecial
                  End If
          End With
    Next i
    
    ws.Range("A1").AutoFilter
    Application.ScreenUpdating = True
    
End Sub

I think that the errors are due to the worksheet variable (ws) still having the value of "Sheet1" and the column to filter on is still 11 whereas I believe the OP intends to use different columns to filter on in different sheets. Perhaps the ws variable needs to be changed to ActiveSheet.
I've also added a check to ensure sheets are not duplicated causing the 'name' error to arise.

@Kellie:

Try Mump's code as per my above modification but please also check that whichever column is filtered does not have illegal characters.
Please also help Mumps to help you by doing as he has requested and supply a sample.

 

[mumps]

@vcoolio
Thank you for your suggestion.