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.


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


    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?

  • 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


    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?

  • 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


  • 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.

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


    So that is what I suggested in my earlier message. If you'd said this before you would have saved me time and you would already have had a working solution.