VBA: Create new sheets based on a certain column

[phroureo]

Hello everyone, I have a column with multiple instances of recurring data (i.e. it says "A" 20 times, "B" 15 times, "C" 1 time, etc.)

What I want is to get (in this example) nine sheets: A 1, B 1, C 1, A 2, B 2, C 2, A 3, B 3, and C 3. The 1, 2, and 3 are static names (in this case, Revenue, COGS, and Expense).

I found an answer here http://www.mrexcel.com/forum/excel-...cations-how-add-worksheet-specified-name.html about how to create sheets based off names in a range. However, my case is different because I need to select the distinct values in the column, instead of having 20 sheets named Admin, 15 sheets named Imaging, and 1 sheet named MPS, I want 1 sheet with each name.

I know that I'm going to have to use a FOR loop for the 1, 2, and 3 parts. I assume that I will also have to use one for the A, B, and C parts as well, but I don't know how to get VBA to select each distinct name from the list.

 

[sijpie]

There are several ways to skin this cat, but this is a simple one (the other is with collections for instance)

<font face=Calibri>Option Explicit<br><br><SPAN style="color:#00007F">Function</SPAN> CheckSheet(sName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><SPAN style="color:#007F00">' Function returns TRUE if sheet sName exists</SPAN><br><SPAN style="color:#007F00">'----------------------------------------------</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> wsWS <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> wsWS <SPAN style="color:#00007F">In</SPAN> Worksheets<br>        <SPAN style="color:#00007F">If</SPAN> LCase(wsWS.Name) = LCase(sName) <SPAN style="color:#00007F">Then</SPAN><br>            CheckSheet = <SPAN style="color:#00007F">True</SPAN><br>            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> wsWS<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> CreateSheets()<br><SPAN style="color:#007F00">' sub runs through entries in column A and _<br>  creates three sheets for each unique entry</SPAN><br><SPAN style="color:#007F00">'----------------------------------------------</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> sName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rIn <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> wsThis <SPAN style="color:#00007F">As</SPAN> Worksheet, wsNew <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Const</SPAN> sREV = " Revenue", sCOG = " COGS", sEXP = " Expense"<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wsThis = ActiveSheet<br>    <SPAN style="color:#00007F">Set</SPAN> rIn = wsThis.Range("A1")    <SPAN style="color:#007F00">'<<<<<<<<<<<<<< Change if in different column</SPAN><br>    <br>    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> Len(rIn.Value)<br>        sName = rIn.Value & sREV<br>        <SPAN style="color:#00007F">If</SPAN> CheckSheet(sName) = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#007F00">' doesn't exist yet, so create the sheets</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> wsNew = Sheets.Add(after:=Sheets(Sheets.Count))<br>            wsNew.Name = sName<br>            <SPAN style="color:#00007F">Set</SPAN> wsNew = Sheets.Add(after:=Sheets(Sheets.Count))<br>            wsNew.Name = rIn.Value & sCOG<br>            <SPAN style="color:#00007F">Set</SPAN> wsNew = Sheets.Add(after:=Sheets(Sheets.Count))<br>            wsNew.Name = rIn.Value & sEXP<br>            <br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rIn = rIn.Offset(1, 0)<br>    <SPAN style="color:#00007F">Loop</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wsNew = <SPAN style="color:#00007F">Nothing</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wsThis = <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>