Creating new worksheets for each cell value under specified column

Hi everyone. In Sheet1 of my workbook, for each unique cell values under Column M (those values are 'alphabets', e.g. "A", "B", "C", etc.), I want to create new Worksheets titled "Class [cell value]", then copy the Sheet1 rows containing the relevant cell values ('alphabets') to the corresponding new worksheets. The problem I am facing now is that I end up creating a single new worksheet titled "Class ". Would appreciate if anyone can help.

Private Sub Test_Click()Dim Ws As Worksheet
Dim i As Integer
Dim ClassRange As Range
Dim ClassCell As Range
Set ClassRange = Sheet1.Columns("M")
For Each c In ClassRange
            
    For Each Ws In Worksheets
               
        If LCase(Ws.Name) = ClassString Then
            
            Sheet1.Range("Q1").Value = "Sheet Found"
            Exit For
                    
        Else
            
            Sheet1.Range("Q1").Value = "Class " & c.Text
                                           
        End If
    
    Next Ws
            
    If Sheet1.Range("Q1").Value = "Sheet Found" Then
    
        Sheet1.Select
    
    ElseIf InStr(1, Sheet1.Range("Q1").Text, "Class", vbTextCompare) = 1 Then
    
        ActiveWorkbook.Sheets.Add Before:=Sheets(Sheets.Count)
        ActiveSheet.Name = Sheet1.Range("Q1").Text
        
        Sheet1.Select
        
    Else
    
        MsgBox ("Error!")
        Exit For
    
    End If
    
Next c
End Sub

Re: Creating new worksheets for each cell value under specified column

Quote from StephenR;773515

The first thing I note is that you don't define ClassString. I can't see anything being copied either. Could you post a sample workbook - makes life a lot easier?

Sure

forum.ozgrid.com/index.php?attachment/69610/

Re: Creating new worksheets for each cell value under specified column

Quote from StephenR;773526

OK, so a couple of things. What is ClassString? There is no data in column M of your file.

By the way, a good practice tip on declaring variables http://www.cpearson.com/excel/declaringvariables.aspx

Sorry, I forgot to say that it should be column B in my sample file instead of column M.

Following your advice, I will define ClassString (as string?) as see if that can get my code to work...

Re: Creating new worksheets for each cell value under specified column

Quote from StephenR;773537

Yes, but you also need to assign it a value. At the moment you are not doing this.

I don't quite understand what value need to be assigned to ClassString?

Totally new to VBA here

Re: Creating new worksheets for each cell value under specified column

Quote from StephenR;773578

You're asking me?! It's a variable you put in your code so it needs a value otherwise it is just null or empty. I don't know what it represents so I don't know what value it should be.

Let's take a step back - explain in plain English what you are trying to achieve. It's not completely clear to me from your example. My best guess - are you saying you want a list of unique values of column B and a separate sheet added for each containing the corresponding column A values?

Sorry for the late reply, I have been really busy for the past few days :dead:

Under Column B, there are four unique values: A, B, C, and S. I want to create 4 new worksheets that are titled "A", "B", "C" and "S", respectively.

Re: Creating new worksheets for each cell value under specified column

That worked perfectly, thanks Stephen!

Now I am trying to extend the codes to, after creating and naming the new worksheets, copy all rows in the MasterSheet where the Class data is "A" to the newly created Worksheet "Class A", then all rows containing "B" under "Class" column into Worksheet "Class B", and so forth. Stuck again after an hour of trying however... further help would be much appreciated.